CODEWITHSUNDEEP
mysqlsqlpostgresql
user740521
user740521

Reputation: 1204

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?

Upvotes: 1

Views: 114

Answers (7)

Abecee
Abecee

Reputation: 2393

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.

Update

As Erwin Brandstetter made explicit: If the data can't be trusted to be inherently consistent (along the lines of your inquiry), one option is:

SELECT map_id FROM (
  SELECT map_id, 'data_x' t
  FROM TableB B JOIN Table_A A ON B.table_a_id = A.id AND A.data_x = 'person'
  UNION
  SELECT map_id, 'data_y'
  FROM TableB B JOIN Table_A A ON B.table_a_id = A.id AND A.data_y = '200'
) T
GROUP BY map_id
HAVING COUNT(DISTINCT t) = 2
;

This should ensure "at least one each". (Alternatives have been suggested by others.) To get "exactly one each", you could try

SELECT map_id FROM (
  SELECT map_id, 'data_x' t, data_y
  FROM TableB B JOIN Table_A A ON B.table_a_id = A.id AND A.data_x = 'person'
  UNION
  SELECT map_id, 'data_y', data_y
  FROM TableB B JOIN Table_A A ON B.table_a_id = A.id AND A.data_y = '200'
) T
GROUP BY map_id
HAVING COUNT(DISTINCT t) = 2 AND COUNT(DISTINCT data_y) = 2
;

See it in action (with additional test data): SQL Fiddle.
And it works in PostgreSQL as well: SQL Fiddle

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

Upvotes: 1

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

Lets do it simple:

SELECT * FROM
(
    SELECT map_id 
    FROM   table_a a1 
    inner join TableB b1 ON a1.id = b1.table_a_id 
    where a1.data_x = 'person'
) as p
inner join
(
    SELECT map_id 
    FROM   table_a a1 
    inner join TableB b1 ON a1.id = b1.table_a_id 
    where a1.data_y = '200'
) as q
on p.map_id = q.map_id

You may replace SELECT * FROM with SELECT p.map_id FROM.
You may add more sub-set-joins to have more conditions.

sql-fiddle

Upvotes: 0

Abelisto
Abelisto

Reputation: 15624

select map_id from
  table_b b 
    left outer join table_a a1 on (b.table_a_id = a1.id and a1.data_x = 'person')
    left outer join table_a a2 on (b.table_a_id = a2.id and a2.data_y = '200')
group by map_id
having count(a1.id) > 0 and count(a2.id) > 0

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

This is less elegant than the INTERSECT solution @Malta posted, but it works with the limited capabilities of MySQL as well:

SELECT b1.map_id
FROM   table_a a1
JOIN   tableb  b1 ON a1.id = b1.table_a_id AND a1.data_x = 'person'
JOIN   tableb  b2 ON b2.map_id = b1.map_id AND b2.table_a_id <> b1.table_a_id
JOIN   table_a a2 ON a2.id = b2.table_a_id AND a2.data_y = '200';

SQL Fiddle for MySQL.
SQL Fiddle for Postgres.

Upvotes: 1

Malta
Malta

Reputation: 544

This situation is a perfect fit for an unusual SQL operator: INTERSECT. It is a very declarative, efficient and elegant solution for this problem.

SELECT Map.map_id
FROM Table_B AS Map JOIN Table_A AS Person ON (Person.id = Map.table_a_id) AND (Person.data_x = 'person')
INTERSECT
SELECT Map.map_id
FROM Table_B AS Map JOIN Table_A AS Amount ON (Amount.id = Map.table_a_id) AND (Amount.data_y = '200')

Formally what you are asking for is exactly the intersection of two disjoint sets: the set of map id's that are persons and the set of map id's that have a value of 200.

Please note the INTERSECT operator does not exists in MySQL, but it does in almost all advanced relational DBMS, including PostgreSQL.

Upvotes: 3

Matt
Matt

Reputation: 14361

Sounds like you want a standard INNER JOIN.

But I do beg to differ on your result:

 map_id if it has an entry in table_a for both data_x = 'person' and data_y = '200'

There is not a record in your data set that has both 'person' and data_y = '200' and therefore no mp_id can be returned

Here is a typical INNER JOIN relating to your narrative.

SELECT DISTINCT
    b.map_id
FROM
    TableA a
    INNER JOIN TableB b
    ON a.id = b.table_a_id
WHERE
    a.data_x = 'person'
    AND a.data_y = '200'

If more than one map_id exists with data_x = 'person' and data_y = '200' then you will get multiple results but only 1 row per map_id

If you want the map_id(s) for records with data_x = 'person' or data_y = '200' then switch the and in the where statement to or and you will receive map_id 7 & 8.

SELECT DISTINCT
    b.map_id
FROM
    TableA a
    INNER JOIN TableB b
    ON a.id = b.table_a_id
WHERE
    a.data_x = 'person'
    OR a.data_y = '200'

Note this encompasses (7,1)(8,2) because 1 & 2 both have data_x = 'person' and then (7,3) because 3 has data_y = '200' therefore it would return map_id 7 & 8.

Upvotes: 0

Shadow
Shadow

Reputation: 34285

Join the 2 tables, group by map_id, use conditional counting with either count() or sum(), and filter in having clause (I use mysql syntax below):

select map_id,
       sum(
           case
               when a.data_x='person' or a.data_y='200' then 1
               else 0
           end
          ) as matches
from a
inner join b on a.id=b.a_id
group by b.map_id
having matches=2

The above query assumes that you cannot have more than one record for any map_id where data_x is person or data_y is 200. If this assumption is incorrect, then you need to use either exists subqueries or 2 derived tables.

Upvotes: 0

Related Questions