CODEWITHSUNDEEP
mysql
Cronas De Se
Cronas De Se

Reputation: 331

Query a distinct value where a columnA != columnB

I'm trying to select the group_ID of groups which ID = '12-027-013' is not a part of.

I have tried this query.

SELECT DISTINCT groups.group_ID, groups.group_name 
FROM groups INNER JOIN group_member 
ON groups.group_ID = group_member.group_ID 
WHERE groups.group_name LIKE "%e%" 
AND group_member.ID != "12-027-013"

But it still displays groups that ID = '12-027-013' are already part of.

These are my tables

TABLE: groups

enter image description here

TABLE: group_member

enter image description here

This is the result of my query

enter image description here

Any help would be much appreciated. Thank you.

Upvotes: 0

Views: 27

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I think the best approach is simply a LEFT JOIN: 

SELECT g.group_ID, g.group_name 
FROM groups g LEFT JOIN
     group_member gm
     ON gm.group_ID = g.group_ID  AND
        gm.ID = '12-027-013'
WHERE g.group_name LIKE '%e%' AND
      gm.group_ID IS NULL;

This doesn't have a DISTINCT, so it should perform much better than your original query.

An similar alternative is NOT EXISTS:

SELECT g.*
FROM groups g 
WHERE g.group_name LIKE '%e%' AND
      NOT EXISTS (SELECT 1
                  FROM group_member gm
                  WHERE gm.group_ID = g.group_ID  AND
                        gm.ID = '12-027-013'
                 );

Both these version can make use of indexes on groups(group_name, group_id) and group_member(group_id, id) for optimal performance.

Upvotes: 1

Meysam Valueian
Meysam Valueian

Reputation: 671

Try this: 

SELECT DISTINCT groups.group_ID, groups.group_name 
FROM groups INNER JOIN group_member 
ON groups.group_ID = group_member.group_ID 
WHERE groups.group_name LIKE "%e%" 
AND group_member.ID NOT LIKE "%12-027-013%" //if the type of id is text

The problem is in "!=". It should be NOT LIKE

Upvotes: 0

mynawaz
mynawaz

Reputation: 1594

try this

SELECT DISTINCT
  groups.group_ID,
  groups.group_name
FROM groups
  INNER JOIN group_member
    ON groups.group_ID = group_member.group_ID
  LEFT OUTER JOIN (SELECT group_id FROM group_member WHERE group_member.ID = "12-027-013") gm ON group_member.group_id=gm.group_id
WHERE groups.group_name LIKE "%e%"
  AND gm.group_id IS null

Upvotes: 1

Related Questions