CODEWITHSUNDEEP
mysqlalgorithmintersection
Gabriel Santos
Gabriel Santos

Reputation: 4974

Union and Intersection of data

I have three tables:

user

id | name
------------------
1  | Foo
2  | Bar
3  | Baz

group_type

id | name
------------------
1  | Group 1
2  | Group 2
3  | Group 3
4  | Group 4
5  | Group 5

user_group

id | user_id | group_type_id | [..]
------------------------------------
1  | 1       | 1             | [..]
2  | 1       | 3             | [..]
3  | 2       | 1             | [..]
4  | 1       | 5             | [..]
5  | 2       | 3             | [..]
6  | 3       | 3             | [..]

Well, currently, I can find all users from a specified list of groups with union, which is like a "or" clause:

SELECT u.*
FROM   user u,
       user_group ug
WHERE  ug.user_id = u.id
       AND ug.group_type_id IN( 1, 3, 5 )

Resulting:

id | name
------------------
1  | Foo
2  | Bar
3  | Baz

Now, I need to intersect the gorup, find all users which have groups of type 1 AND 3, resulting:

id | name
------------------
1  | Foo
2  | Bar

I have tried some queries, but don't imagine a way of doing this correctly.

Upvotes: 1

Views: 86

Answers (3)

Spencer Ewall
Spencer Ewall

Reputation: 189

I'm not sure if my syntax is perfect, but I'd reccomend self-joining user_group onto itself using user_id and forcing one of the selected entries (ug1 and ug2) to have ug1.group_type_id=1 and the other ug2.group_type_id=3. This gives you all user_id's with 1 AND 3 as their group_type_id. Now that you have that, you can do another join onto your user table, giving you all of the results that you were looking for.

SELECT u.* 
FROM user u 
JOIN (SELECT ug1.user_id 
    FROM user_group ug1 JOIN user_group ug2 
    ON ug1.user_id=ug2.user_id
    WHERE ug1.group_type_id=1 and ug2.group_type_id=3) ug
ON u.id=ug.user_id

Upvotes: 0

schizodactyl
schizodactyl

Reputation: 1455

SELECT u.id, u.name
FROM user u 
INNER JOIN user_group g 
  ON u.id = g.user_id 
WHERE ug.group_type_id IN (1,3) 
GROUP BY u.id, u.name 
HAVING count(distinct ug.group_type_id) = 2

Not as clean as the normal case, but it's certainly possible.

Upvotes: 3

Imane Fateh
Imane Fateh

Reputation: 2406

Try to use INTERSECT query. The syntax for the SQL INTERSECT query is:

select field1, field2, ... field_n
from tabl,tab2...
INTERSECT
select field1, field2, ... field_n
from tablel,table2...

Upvotes: 1

Related Questions