handling null values on select query mysql

Question

table stockholders

stockholder_id    election_id    user_id    
1                 1              1                          
2                 1              2          
3                 2              3

table users

user_id           user_type
1                 1
2                 1
3                 1
4                 1
5                 1

select 
    * 
from 
    tbl_users
left join tbl_stockholders
    on tbl_stockholders.user_id = tbl_users.user_id
where 
    user_type=1 
    and stockholders.user_id is null 
    and election_id <> 1

i want to search election_id not equal to 1 and user type equal to 1

stockholder_id    election_id    user_id      user_type
3                 2                 3            1         
null              null              4            1
null              null              5            1

this is an update

sorry my problem should be excluding the tbl_stockholders from the tbl_users with the parameter election_id.. because a problem exist when i have a duplicate user_id

table stockholders

stockholder_id    election_id    user_id    
1                 1              1                          
2                 1              2          
3                 2              3   
4                 1              3

in the previous answer this is the result when election_id<>2

stockholder_id    election_id    user_id      user_type
3                 1                 3            1         
null              null              4            1
null              null              5            1

this must be

stockholder_id    election_id    user_id      user_type      
null              null              4            1
null              null              5            1

this is my current not working code

select * from tbl_users where not exists (select * from tbl_stockholders where election_id <> 2)

Why do you have the predicate tbl_stockholders.user_id is null in your query? Especially since tbl_stockholders is on the left of your left join? — eggyal
– eggyal, Commented Oct 28, 2013 at 15:39
tbl_users.user_id is null to exclude the user in the tbl_stockholders sorry is tbl_users not tbl_stockholders @eggyal — user1046065
– user1046065, Commented Oct 28, 2013 at 15:47

Mosty Mostacho · Accepted Answer · 2013-10-28 15:58:52Z

1

Try this:

SELECT * FROM users u
LEFT JOIN stockholders s ON u.user_id = s.user_id
WHERE u.user_type = 1 AND (s.election_id <> 1 OR s.election_id IS NULL)

Fiddle here.

edited Oct 28, 2013 at 15:58

answered Oct 28, 2013 at 15:52

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1046065 Over a year ago

SELECT * FROM users u LEFT JOIN stockholders s ON u.user_id = s.user_id WHERE u.user_type = 1 AND (s.election_id <> 1 OR s.election_id IS NULL)

user1046065 Over a year ago

thanks.. thats works.. just changing election_id=2 to election_id<>1

Mosty Mostacho Over a year ago

That is correct: The condition should be <> 1 rather than = 2. Due to the low amount of data the results matched :)

zulqarnain · Accepted Answer · 2013-10-28 15:54:54Z

0

I think, this is what you should do: select * from tbl_stockholders left join tbl_users on tbl_users.user_id = tbl_stockholders.user_id where (user_type=1 and election_id <> 1) I don't think you intend to select null user_ids. Also that would invalidate the join i.e you are saying joined the two tables based user_id but select null user_id from the first table.

answered Oct 28, 2013 at 15:54

zulqarnain

1,7452 gold badges16 silver badges35 bronze badges

1 Comment

user1046065 Over a year ago

hmm.. tbl_stockholders is the selection of users on tbl_users , then i want to search a users does not exist in tbl_stockholders. and not equal election_id ,, sorry for my english i cant explain it clearly.. sorry

Collectives™ on Stack Overflow

handling null values on select query mysql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related