CODEWITHSUNDEEP
mysqlsql
Beginner
Beginner

Reputation: 29543

MySQL query - Getting people out that arent in another table?

i hope i explain this correctly.

What i have is this query..

SELECT *  FROM classesbooked 
JOIN name ON NameNo = classesbooked_nameno
Group By classesbooked_nameno

This gets me out all the names of people who have booked a class. What i want is the opposite. All the people who are in table 'name' but not in table 'classesbooked'

?

Upvotes: 1

Views: 40

Answers (4)

Ovais Khatri
Ovais Khatri

Reputation: 3211

Try this: 

   Select * from name tbl
        where tbl.NameNo 
        not in (select t.classesbooked_nameno 
                 from classesbooked_nameno t)

Upvotes: 1

josh.trow
josh.trow

Reputation: 4901

SELECT * 
FROM name 
  LEFT JOIN classesbooked 
    ON NameNo = classessbooked_nameno 
WHERE classesbooked_nameno IS NULL

Upvotes: 0

a'r
a'r

Reputation: 36999

Use a left join and select records where the second table's join column is null. 

select *
from name n
    left join classesbooked c on n.NameNo = c.classesbooked_nameno
where c.classesbooked_nameno is null

Upvotes: 2

George Johnston
George Johnston

Reputation: 32258

One of the ways you can accomplish this is with a sub query:

SELECT *
FROM name
WHERE NameNo NOT IN (
                       SELECT 
                          classesbooked_nameno
                       FROM classesbooked
                    )

Essentially this says to return everything in table name that does not have an associated Id in classesbooked

Upvotes: 1

Related Questions