mysql join and null

Question

I have 2 tables "browsers" and "votes". I need all the browsers and only the votes by a vote_ranking and a user_id. Sometimes there is no votes but i still need the browsers. That's the code i wrote:

SELECT browsers.*, votes.*
FROM browsers LEFT JOIN votes ON browsers.app_id = votes.app_id
WHERE vote_ranking = '$ranking' AND user_id = $userid or vote_id IS NULL

The problem is when there is no votes (vote_id IS NULL) the app_id is changed to NULL. But i need to keep the browsers app_id even there's no app_id in votes.

Can i join the table votes only on rows where the're is a vote_id ? Or anything similar to do what i need ?

Thanks

Dmytro Shevchenko · Accepted Answer · 2012-04-12 21:11:52Z

1

Try this:

SELECT browsers.*, votes.vote_ranking, votes.user_id
FROM browsers
LEFT JOIN votes
  ON browsers.app_id = votes.app_id
  AND vote_ranking = '$ranking' 
  AND user_id = $userid

This way, a corresponding record from votes will be joined only if it has appropriate vote_ranking. Otherwise there will be NULLs, but you will still keep the data from browsers.

edited Apr 12, 2012 at 21:11

answered Apr 12, 2012 at 19:52

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

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

6 Comments

Azzoth Over a year ago

It does not keep the browsers data when there's no vote with a matching app_id, the browser row don't show up. vote_ranking is only in the votes table. The only matching data is app_id.

Dmytro Shevchenko Over a year ago

Have you tried my query? I definitely think it should work for you.

Azzoth Over a year ago

Yes i did and it only show the browsers with votes. The problem is in your WHERE it only show the rows with a user_id but when there is no vote, there is no user_id so the browser don't show up.

Dmytro Shevchenko Over a year ago

Ah. I didn't realize user_id was in the votes table. I'll fix the query now. Try it again.

Azzoth Over a year ago

Yes it's better but the id is still overwriting to null. Maybe the problem is in later. I mysql_fetch_assoc and when i use the $row["app_id"] it's blank. But i work with only the browser table it's not a code error it's the join that overwrite the app_id.

|

Collectives™ on Stack Overflow

mysql join and null

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related