nested query.

Hallo everybody,

I'm currently coding some interface using Qt as a C++ developer. My sql-knowledge is quite lousy (Obviously that brought me here). Right now I'm facing some database-challange. I really like to improve my sql later. But for now I need a quick solution not to go bananas. I'd really appreciate your help. I will learn sql. Promise.

I have 2 tables, 'requests' and 'productions'. I need to return the names of all productions taking place at a given day.
Now: 'requests' gives me only all production -IDs on that specific day. These IDs I need to use later in the next query on 'productions'. I thought to use following statement:

requests:
date      ID
date-1   0
date-1   1
date-1   2
date-2   3
date-3   4
date-3   5
...


productions:

ID title
0 soccermatch
1 chesschampionship
2 rammstein-concert
3 tv-show
...


SELECT `title` FROM `productions` WHERE `P_ID` = (SELECT `P_ID`, FROM `requests` WHERE  `date` = {d 'date-1' } )

but it fails. I guess it's because the inner statement returns more than one value. How would you solve this efficiently. The Datatbase is VERY big.

regards and thanx in advance, Lars
Lars C++ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Simple join?

select title from productions p join requests r on p.id=r.id and date = Convert(DATE, GetDate())
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
You pretty much had it, just use IN rather than = to check more than one value:

SELECT title
FROM dbo.productions
WHERE P_ID IN (
    SELECT P_ID
    FROM dbo.requests
    WHERE  date = {d 'date-1' } ) /* date = @date */
0
Lars C++ProgrammerAuthor Commented:
Thanks so far. Great help. I didn't get that JOIN thing yet. I tried the nested version of Scott as follows (in german letters)

SELECT `produktionstitel`
FROM `dbo_Produktion`
WHERE 'P_ID'
IN (
SELECT `P_ID`
FROM `dbo_LeitungAnforderung`
WHERE `anfDatum` = {d '2018-02-06' } )

It returns a datamissmatch error. Why?

The beer is on me.
Cheers, Lars
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
Maybe anfDatum is not a date but a datetime?

SQL will generally allow that for normal literals or @variables, but I'm not sure about ODBC / API format of {d ...}.

Can you just do:

WHERE `anfDatum` = '20180206' )

Format YYYYMMDD is always valid for any date/datetime in SQL Server.
0
Lars C++ProgrammerAuthor Commented:
Hey Scott, out of Office now. First thing tomorrow. Second will be a bookstore.

Thanks guys.
0
Lars C++ProgrammerAuthor Commented:
Hi again,

this one itself,

SELECT `P_ID` FROM `dbo_LeitungAnforderung` WHERE `anfDatum` = {d '2018-02-06' }

not contained in an IN-statement, returns a colum of values like

335618
234567
136578
123855
...

perhaps the problem here is the column-form?

Lars
0
Scott PletcherSenior DBACommented:
I have no idea.  I have no idea what the date for those columns look like or what the P_ID value means (or implies).  Please keep in mind that I have no knowledge of your data, none.
1
Lars C++ProgrammerAuthor Commented:
Oh yeah, you are absolutely right. Sorry for that. To be more specific: the Statement I am using inside the IN brackets returns a column of integers. As I read IN must be run with values split by commata. A column most likely is split by semicola. Is that right? And if so...how do I transpose that column. (Before I finally go to a bookstore)

Regards, Lars
0
Scott PletcherSenior DBACommented:
IN accepts a list of values.  if they are literal/hard-coded values, they will be separated by commas, like this:
WHERE id IN (1, 2, 3)
WHERE name IN ('Bill', 'Mike', 'Sam')

But, If a single value contains a list, it must be split into individual values for the IN.  That is, this:

DECLARE @id_values varchar(40)
SET @id_values = '1, 2, 3'

WHERE ID IN (@id_values)
does NOT work.   You would have to use a text splitter, such as dbo.Delimited8KSplit, to make each entry a separate value.
0
slightwv (䄆 Netminder) Commented:
>>As I read IN must be run with values split by commata

I'm confused.

If you provide a list, it needs to be comma separated BUT it can also take a result set from a sub query like the one provided.
0
Scott PletcherSenior DBACommented:
What is the data type of column anfDatum?  Is it a date / datetime?  Or is it char/varchar, which would make it much harder to match properly.
0
Lars C++ProgrammerAuthor Commented:
Yeah guys, I'm confused too. The 'anfDatum' is a dateTime in which only the date is updated. But that doesn't seem to be the problem. Becaus outsite the IN it works fine.
This is a very old and dirty database I'm working on.
Perhaps, since I'm coding the query in C++, I will do it in two steps. I just wanted to do it slim, but appearently I'm not clever enough (yet).

Cheers, Lars
0
Scott PletcherSenior DBACommented:
If it's a datetime, maybe you can't specify a date using the {d } format.

Again, if you can, just specify the date string as a literal only, preferably with no dashes, 'YYYYMMDD':

WHERE `anfDatum` = '20180206'

Or, if you can't avoid it:

WHERE `anfDatum` = '2018-02-06'
0
Lars C++ProgrammerAuthor Commented:
Thanks for the patience Scott. I already tried that. But it didn't work. At least not as an IN statement. Alone it worked as I said in one of my previous posts.
I think I have a deeper deficit regarding literals, numeric values and pointers here. My learning added value is: sql is not C++. I'll clear that and then I come back. I don't even know how to test or even debug some of my sql code. I only know "it's wrong" and nothing else. Some online code-checking machines tell me it's in line xxx,  but not what or how. Little frustrating. I think I'll start over.

Thanks, Lars
0
slightwv (䄆 Netminder) Commented:
I notice some of the SQL you post has smart-quotes and single quotes in places I wouldn't expect them.

Are you trying to debug the SQL inside of C++ code?

If so, get the SQL running first outside of code:
I don't run SQL Server but there should be a TSQL tool or similar tool that will connect directly to the database to test out the SQL.

Worst case, SQL Fiddle:
http://sqlfiddle.com/#!18
0
Lars C++ProgrammerAuthor Commented:
Are you trying to debug the SQL inside of C++ code?

Oh no, no. I tried to say that, my two statements alone work fine. So I'll do it quick and dirty. Use one statement, store result in my c++ prog and then perform second query. Worst case in a while or for loop. No better idea.

Can you give me an example of the smart and single quotes confusion I'm on. Please remember: I'm a complete newbie here. Like to learn.

Thank you, Lars
0
slightwv (䄆 Netminder) Commented:
>> Can you give me an example of the smart and single quotes confusion

this:
SELECT `P_ID` FROM `dbo_LeitungAnforderung` WHERE `anfDatum` = {d '2018-02-06' }

versus:
SELECT P_ID FROM dbo_LeitungAnforderung WHERE anfDatum = {d '2018-02-06' }

>>Worst case in a while or for loop. No better idea.

Don't write in code what you can do in SQL.  I'm sure we are missing something simple.

I gave up on trying to continue my JOIN post but can you explain what you meant by "I didn't get that JOIN thing yet"
0
Lars C++ProgrammerAuthor Commented:
I gave up on trying to continue my JOIN post but can you explain what you meant by "I didn't get that JOIN thing yet"

sure. thanks also to you for the patience with me. It is very simple: I have no clue about sql and I don't want to ask worthless questions more than really neccessary. I need to go thru it because it is part of my C++ project. So I thought it was easier to go with something more familiar. The IN I could gradually understand. But join to me is very abstract.
That was nothing of a rating. I appreciate every help but I couldn't understand the syntax. IN seemed to be easier.
Perhaps we give it a try. If you could just help me a little to recode my query as a join statement. In the meantime I'll try to find out what it is all about.

regards, Lars
0
slightwv (䄆 Netminder) Commented:
But the IN query isn't working either?

The reason I came back to it is the SQL is simpler and if it returns the same rows, it should be easier to incorporate into your C++

Joins shouldn't be difficult if you've worked  with sets of data in any programming language.

See if examples with Visuals help:
https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

There are MANY examples out there if you look for:  join venn diagram

>> I tried to say that, my two statements alone work fine.

Have you tried the IN syntax outside of C++ code?  Run it in a tool that connects directly to the database.  Then you aren't trying to figure out if the error is in the SQL or the C++ code.
0
Lars C++ProgrammerAuthor Commented:
Oh yes, I'm on libreOfficeBase. That's sufficient for me. If you use their designer mode to create queries you get that useless brackets and quotes. I removed them just now. All the same.
Anyway, my C++ didn't say anything different. I run the programm and the C++-lib returns the same error. Datamissmatch here. I'm really astonished, how much time I had to spend on that topic without clearing that issue. But I see it as a personal progress. :-)

So, I'll have a break and then go to https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

best, Lars
0
slightwv (䄆 Netminder) Commented:
I would try the SQL without any quotes of any kind.  For testing purposes I would also remove the where clause.

Try this in your code:
SELECT title FROM productions WHERE P_ID in (SELECT top 1 P_ID  FROM requests)


I'm not familiar with libreOfficeBase with from a quick Google it doesn't appear it uses native SQL Server drivers.  So, there is no guarantee the SQL it produces will run against a native driver.  It only shows it will run against itself.

I'm not a SQL Server person so I cannot speak to all the available tools that will allow a direct connect to the database.  Hopefully Scott can offer some suggestions on what tool is best

I doubt you want to take time to learn a new tool but I would really test the SQL directly against the database.  

Here is a list from Microsoft:
https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools
0
Lars C++ProgrammerAuthor Commented:
SELECT `p`.`produktionstitel` FROM `dbo_Produktion` `p`, `dbo_LeitungAnforderung` `r` WHERE `p`.`P_ID` = `r`.`P_ID` AND `r`.`anfDatum` = {d '2018-02-06' }

Open in new window


Party ON !

Finally the JOIN saved my sorrier existenz. Thanks to you.

Off to the bookstore...Lars
0
Lars C++ProgrammerAuthor Commented:
Oh my,...If I knew before, I would have read the wonderful manual.
0
slightwv (䄆 Netminder) Commented:
I prefer the ANSI join syntax that I posted but glad it worked for you.

Remember:  Everything is hard until you learn it!
1
Lars C++ProgrammerAuthor Commented:
I coded in JOIN syntax, strongly following your post from the beginning of this tread. Then I used the designer again. It removed it and spit out my posted solution.

:-)
0
slightwv (䄆 Netminder) Commented:
>>Then I used the designer again. It removed it and spit out my posted solution.

Then the tool is old and doesn't understand ANSI syntax.  I find the ANSI syntax more human readable than the old way.

I would suggest you stay away from code generators that say they will save you time.  They are only as good as their developers and last update and in my opinion, NEVER write efficient SQL.

ANSI joins have been around for quite a while.

Instead of taking what we post and running it through some other program before using it, did you try a straight copy/paste?
0
Lars C++ProgrammerAuthor Commented:
No I didn't. Yet. I have to correct myself a little. I'm on libreBase connecting to a MSaccess.mdb.  In a way... Gladly it works at all. And I learned a lot here. I'll experiment around a little with it when I'm done.

lars
0
Lars C++ProgrammerAuthor Commented:
Thanks for the pleasent contact again.
0
slightwv (䄆 Netminder) Commented:
>>connecting to a MSaccess.mdb

So this never had anything to do with SQL Server?

In the future, you need to pay attention to the Topic Areas the question is asked in.  If you ask in a SQL Server Topic Area, you'l get SQL Server answers.  You might also want to mention in the question what products are involved in case the Topic Area is incorrect.  Someone will make sure it ends up in front of the proper Experts.
0
Lars C++ProgrammerAuthor Commented:
You see my misery. I didn't even know that.  i just came to a point where I had to acquire some data.
Some posts before I said, I'll come back a little wiser. That was a promise.
Thanks again also for that update.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.