user740521 user740521 - 4 months ago 7
SQL Question

How can I accomplish the following in SQL?

I have two tables.

table_a:

id | data_x | data_y
--------------------
1 person joe
2 person bob
3 amount 200
4 addres philville


tableB:

map_id | table_a_id
-------------------
7 1
7 3
7 4
8 4
8 2


The result I want is the map_id if it has an entry in table_a for both data_x = 'person' and data_y = '200'

So with the above table B, the result should be

map_id
------
7


How can I write that query in SQL?

Answer

Based on your input, the following should get you started using MySQL:

SELECT
  map_id
FROM TableB
JOIN Table_A
  ON TableB.table_a_id = Table_A.id
  AND
  ((Table_A.data_x = 'person')
   OR
   (Table_A.data_y = '200')
  )
GROUP BY map_id
HAVING COUNT(table_a_id) = 2
;

See it in action: SQL Fiddle.

Please comment if and as this requires adjustement / further detail.