The best One to many SQL SELECT

[sunaj]

Maybe I should post this in a diffent forum, but I've coded my database in VB (ADO2.5), so I'll try here...

I have a one to many relation:
Table1
ID
Datafield1
Table2
ID
Table1ID
Datafield2

I want to select all DataField1 (Table1) where the DataField2 (Table2) is X AND Y AND Z.

This can be done by :
SELECT DataField1 FROM Table1 WHERE Table1ID IN (SELECT Table1ID FROM Table2 WHERE DataField2='X') AND
Table1ID IN (SELECT Table1ID FROM Table2 WHERE DataField2='Y') AND
Table1ID IN (SELECT Table1ID FROM Table2 WHERE DataField2='Z')

I don’t have just X,Y,Z but many (up to 100), which will makes up an extremely long and probably not very efficient SQL statement.

Do you know how to make this smarter?

Thank in advance,
Sunaj

 

[NipsMG]

No need to execute so many commands...

I'd streamline it like this:

SELECT DataField1 FROM Table1 WHERE Table1ID IN (SELECT Table1ID FROM Table2 WHERE DataField2 in ('X', 'Y', 'Z')

I've done this with LARGE amounts of data (like over 200) and it executes pretty efficiently.

Hope this helps!


--NipsMG

 

[sunaj]

Nope NipsMG

That'll give you all DataField1 wher DataField2 is X OR Y OR Z, which is not the same as Z AND Y AND Z.


....Other suggestions?

Sunaj

 

[NipsMG]

Ok ok, that's true...

So why not just do:

SELECT DataField1 FROM Table1 WHERE Table1ID IN (SELECT Table1ID FROM Table2 WHERE DataField2 ='X' AND DataField2 =  'Y' AND DataField2 =  'Z'

??

--NipsMG

(Unless I'm totally off base understanding the question..)

 

[sunaj]

In Table2 there is only one DataField2 in each row and it can't be both Y AND Z AND X. e.i. your statement will allways return empty.

Maybe I'm not good at illustrating the problem, but remember that it's a one to many relation: There are many rows in Table2 with the same Table1ID.

I want to select all DataField1 (Table1) for which there exist corresponding (e.i. table1ID match) DataField2 (Table2) with values of X AND Y AND Z.

Sunaj

 

[NipsMG]

hmm....

SELECT
    Table1.DataField1 
FROM
    Table1, Table2
WHERE
    Table1.Table1ID = Table2.Table1ID and (DataField2 in ('X', 'Y', 'Z')

?? This SHOULD work.. From your description it seems you want EVERYTHING from Table2 where Table1.Table1ID = Table2.Table1ID and DataField2 = 'X' or 'Y' or 'Z'


i.e. results like

TABLE1              TABLE2
Table1ID            Table1ID     DataField2
233                  233         'X'
233                  233         'Y'
233                  233         'Z'
244                  244         'X'
255                  255         'Z'

etc etc....

Otherwise I'm totally lost.

I hope THIS is what you were looking for.

--NipsMG

 

[sunaj]

Sorry, but no:

In your example I want '233' because its the only Table1ID where there are rows in Table2 with DataField2='X' AND DataField2='Y' AND DataField2='Z'

Sunaj

 

[NipsMG]

AHHHH!

(sorry it's way too early in the morning for me..)

there is a SLIGHTLY MORE EFFICIENT way to do this...

SELECT DataField1, Table1ID_1 
FROM Table1 
WHERE EXISTS 
(SELECT Table1ID Table1ID_2 FROM Table2 WHERE DataField2='X' AND Table1ID_1 = Table1ID_2) AND 
EXISTS 
(SELECT Table1ID Table1ID_2 FROM Table2 WHERE DataField2='Y' AND Table1ID_1 = Table1ID_2) AND 
EXISTS 
(SELECT Table1ID Table1ID_2 FROM Table2 WHERE DataField2='Z' AND Table1ID_1 = Table1ID_2)

This way you're restricting the amount of records retrieved from Table2 in your subselect statements.. it's slightly more efficient, but basically the same thing you were doing before...

Hope THIS helps..!

 

[sunaj]

Hi NipsMG

Thanks for your help.
It dosen't exactly make the code more compressed (with up to 100 things to look for I'll get an awefull long SQL statement), but I might change it anyway.

Sunaj