CODEWITHSUNDEEP
phpmysqljoin
Alvian Casablancas
Alvian Casablancas

Reputation: 93

Showing all records in JOIN clause in database

I have two tables, named voter and log_vote. Voter contains data of voters while log_vote contains data of voters who have voted.

Now I'm trying to show the voters who haven't voted. I have tried using JOIN in both tables but it will fail since JOIN clause only shows voters that have voted of course.

Here's the code snippet which I've tried:

SELECT * FROM voter INNER JOIN log_vote ON log_vote.nim = voter.nim

How to show only voters who haven't voted? Is using JOIN a correct way to do? Thanks in advance, I will really appreciate for the answer!

*NOTE: I'm using PHP Technology. Wonder if there's any way that PHP can do with my problem.

Upvotes: 0

Views: 44

Answers (4)

Mr-Msd
Mr-Msd

Reputation: 23

you can use

NOT IN

this code should work!

SELECT * FROM voter v WHERE v.nim NOT IN (SELECT lv.nim FROM log_vote lv)

you should give more information about the attribute of each tables are you sure they have the attribute nim in each?

Upvotes: 1

Edwin Alex
Edwin Alex

Reputation: 5108

You can do this also..

SELECT * FROM voter LEFT JOIN log_vote ON log_vote.nim = voter.nim 
WHERE log_vote.nim IS NULL

Upvotes: 1

sagi
sagi

Reputation: 40481

This can be done with a LEFT JOIN, that way the join condition won't filter those who dont answer it, and then you filter all those who didn't have a match like this:

SELECT voter.* FROM voter
LEFT JOIN log_vote ON log_vote.nim = voter.nim
WHERE log_vote.nim is null

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think the most direct method is not exists:

select v.*
from voter v
where not exists (select 1 from log_vote lv where lv.nim = v.nim);

If you want to use join, then the appropriate version is left join:

select v.*
from voter v left join
     log_vote lv
     on lv.nim = v.nim
where lv.nim is null;

Upvotes: 1

Related Questions