CODEWITHSUNDEEP
sqlsql-serverjoinset-theory
Christian A. Rasmussen
Christian A. Rasmussen

Reputation: 290

T-SQL - Find distinct values in table only if joined rows satisfy a list of criterias

This is quite a mouthfull for me. One of my challenges is that I don't know how to formulate the question - which is obvious by the title.

I'll try to illustrate my problem:

I have a table, A:

ID    LocationID
11    185
12    185
13    206

And a table B:

ID    AID    Position    Value
1     11     1           4
2     11     3           8
3     11     5           4
4     12     1           4
5     12     2           4
6     12     3           5

Table B is associated to table A by ID and AID. I would like to construct a query which has the following filters: Position = 1 AND Value = 4 and Position = 3 AND Value = 5 and which gives me a list of distinct IDs from A.ID which stasify all the given criterias.

With this I mean that if I join the two tables together with an INNER JOIN, I only wish to have A.ID = 12.

My own start to solving this problem was something along the lines of:

SELECT DISTINCT A.ID
FROM A
    INNER JOIN B ON (A.ID = B.AID)
WHERE
    A.LocationID = 185 AND
    (B.Position = 1 AND B.Value = 4) OR
    (B.Position = 3 AND B.Value = 5)

Which obviously doesn't work. I thought I had a clear solution to this but when I come to think of it, I really don't.

I'm a bit stumped by this problem and I'm having a hard time searching for a strategy on how to solve it since I don't even know what keywords to use in my search.

Upvotes: 1

Views: 48

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93724

You missed to add parenthesis.

This is how your is executed.

(A.LocationID = 185
       AND  ( B.Position = 1
               AND B.Value = 4 ) )
              OR ( B.Position = 3
                   AND B.Value = 5 )

AND has higher precedence than OR. Try this

SELECT DISTINCT A.ID
FROM   A
       INNER JOIN B
               ON ( A.ID = B.AID )
WHERE  A.LocationID = 185
       AND ( ( B.Position = 1
               AND B.Value = 4 )
              OR ( B.Position = 3
                   AND B.Value = 5 ) )

Upvotes: 0

terary
terary

Reputation: 1098

SELECT DISTINCT A.ID
FROM A
WHERE A.ID IN(
    SELECT ID 
FROM B
WHERE    (B.Position = 1 AND B.Value = 4) OR
    (B.Position = 3 AND B.Value = 5)

)

If I understand you correctly.

SELECT DISTINCT A.ID FROM A INNER JOIN B ON (A.ID = B.AID) WHERE A.LocationID = 185 AND (B.Position = 1 AND B.Value = 4) OR (B.Position = 3 AND B.Value = 5)

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

You can do this with GROUP BY and HAVING:

SELECT A.ID
FROM A
    INNER JOIN B ON (A.ID = B.AID)
GROUP BY A.ID
HAVING MAX(CASE WHEN A.LocationID = 185 THEN 1 END) = 1
   AND MAX(CASE WHEN B.Position = 1 AND B.Value = 4 THEN 1 END) = 1 
   AND MAX(CASE WHEN B.Position = 3 AND B.Value = 5 THEN 1 END) = 1

Demo: SQL Fiddle

Actually can move the LocationID criteria to WHERE:

SELECT A.ID
FROM Table1 A
    INNER JOIN Table2 B ON (A.ID = B.AID)
WHERE A.LocationID = 185
GROUP BY A.ID
HAVING MAX(CASE WHEN B.Position = 1 AND B.Value = 4 THEN 1 END) = 1 
   AND MAX(CASE WHEN B.Position = 3 AND B.Value = 5 THEN 1 END) = 1

Upvotes: 1

Related Questions