CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
user1477929
user1477929

Reputation:

SELECT WITH LEFT JOIN AND WHERE CLAUSE

I have 2 tables: Users{username, UserID} AND Prizes{UserID, prize, status}

I want to select all the users (from Users Left Join Prizes) except the users who has 'status = dead' in the Prizes table

Upvotes: 0

Views: 165

Answers (2)

John Woo
John Woo

Reputation: 263713

I think you need here is INNER JOIN because you only want to search fors user having status not equal to dead.

SELECT  a.*, b.*
FROM    Users a
        INNER JOIN Prizes b
            ON a.userID = b.UserID
WHERE   b.status <> 'dead'

To further gain more knowledge about joins, kindly visit the link below:

UPDATE 1

SELECT  a.*, b.*
FROM    Users a
        LEFT JOIN Prizes b
            ON a.userID = b.UserID
WHERE   b.UserID IS NULL OR b.status <> 'dead'

Upvotes: 4

swapnil
swapnil

Reputation: 81

Try this;

select u.* 
from users as u 
left join prizes as p 
on u.userid = p.userid 
where p.status <>'dead';

Thanks

Upvotes: -1

Related Questions