Melvin Melvin - 2 months ago 9
MySQL Question

SQL query to select row from one table which is not in another table

Registraion and group_members

Registration Table
id name
-------
1 A
2 B
3 C
4 D

group_members Table
name Gid
-------
A 01
B 01
C 02


I need to get the names from registration table which are not a member in group_members table with Gid is 02.
The output must be obviously A,B and D.
But I dont know how to achieve this.Please help.Thanks

Answer

Try this:

SELECT t1.*
FROM Registration AS t1
LEFT JOIN Group_members AS t2 ON t1.name = t2.name AND t2.Gid = '02'
WHERE t2.name IS NULL 

This will filter out any matching records in group_members table with Gid = '02'.

Comments