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.
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