CODEWITHSUNDEEP
mysql
newbie
newbie

Reputation: 1980

mysql getting intersection of one table to three tables

I have a table structure like this:

units
+---------+---------+
|    id   |   unit  |
+---------+---------+
|    1    |   lbs   |
+---------+---------+
|    2    |   cm    |
+---------+---------+
|    3    |   lit   |
+---------+---------+
|    4    |   cum   |
+---------+---------+

material
+----+---------+--------------+
| id | unit_id | material_name|
+----+---------+--------------+
| 1  |    3    |      pcb     |
+----+---------+--------------+
| 2  |    4    |      lbc     |
+----+---------+--------------+

equipment
+----+---------+--------------+
| id | unit_id |equipment_name|
+----+---------+--------------+
| 1  |    1    |      ber     |
+----+---------+--------------+
| 2  |    4    |      war     |
+----+---------+--------------+

project_items
+----+---------+--------------+
| id | unit_id | project_name |
+----+---------+--------------+
| 1  |    1    |      sec     |
+----+---------+--------------+
| 2  |    3    |      dum     |
+----+---------+--------------+

I want to get only the unit_id where only exist in other 3 tables

enter image description here

I tried using inner join

SELECT u.id FROM  units u 
INNER JOIN material m ON u.id = m.unit_id
INNER JOIN equipment e ON u.id = e.unit_id
INNER JOIN project_items i ON u.id = i.unit_id;

but this only select id that intersect on all tables, any ideas?

Upvotes: 1

Views: 1473

Answers (2)

attila
attila

Reputation: 2219

There are probably quite a few ways of doing this, but this can be as simple as using EXISTS, like so:

SELECT u.id 
FROM  units u 
WHERE EXISTS (
    SELECT * FROM material m
    WHERE  u.id=m.unit_id)
  OR EXISTS (
    SELECT * FROM equipment e
    WHERE  u.id=e.unit_id)
  OR EXISTS (
    SELECT * FROM project_items I
    WHERE  u.id = i.unit_id)

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Here's one way to do it using IN with UNION ALL:

select *
from units
where id in (
  select unit_id from material
  union all
  select unit_id from equipment
  union all 
  select unit_id from project_items)

Upvotes: 1

Related Questions