CODEWITHSUNDEEP
mysql
Krzysztof Dk
Krzysztof Dk

Reputation: 155

Getting elements from linking table

I have a table that assign elements to groups. Each element can exist in many groups and can be assigned few times to the same group

it looks like:

element | group
      1 | 1
      1 | 2 
      1 | 3
      2 | 1
      2 | 3
      3 | 2

I'm looking for a simple query that can return me elements that are assigned to group 1, but NOT to group 2.

According to the presented data above this will be element 2.

Upvotes: 0

Views: 43

Answers (3)

juergen d
juergen d

Reputation: 204766

select distinct element 
from  your_table
where element not in (select element 
                      from your_table 
                      where `group` = 2)
and `group` = 1

Upvotes: 2

Maksym Polshcha
Maksym Polshcha

Reputation: 18358

Like that:

select element from table 
where group = 1 and not exists 
(select element from table t 
 where t.element=table.element and t.group = 2)

Upvotes: 0

bobwienholt
bobwienholt

Reputation: 17610

SELECT *
FROM `table` t1
LEFT OUTER JOIN `table` t2
   ON t1.element = t2.element
   AND t2.`group` = 2
WHERE t1.`group` = 1
   AND t2.`group` IS NULL

Upvotes: 0

Related Questions