CODEWITHSUNDEEP
mysqlsqljoin
Helto
Helto

Reputation: 595

Join condition in mysql need to ignore null field

I have two tables: post, post_image

I am currently trying to join them like so:

$sql = 'SELECT post.*, post_image.name AS img FROM post, post_image 
WHERE post_image.postId=post.id LIMIT 10';

Here's my problem. Some posts do not have any entries in post_image, but I still need them returned as well. I know that mysql can check for null, but I'm not sure where it would go in my statement if that is the solution. I'm also not entirely sure I can do this with the shorthand join I am using.

Please help :)

Edit:

This is working as expected now, but I also need to make sure that it only pulls the post_image with field ordinal=0 as there can be multiple post_image entries. I tried adding a WHERE clause and it seemed to only pull posts with images.Here's what I have:

SELECT post.*, post_image.name AS img FROM post LEFT JOIN post_image ON post_image.postId=post.id WHERE post_image.ordinal=0 LIMIT 10

Upvotes: 0

Views: 712

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Use LEFT JOIN like this:

SELECT 
  post.*, 
  post_image.name AS img 
FROM post
LEFT JOIN post_image ON post_image.postId = post.id 
LIMIT 10;

Your query is INNER JOIN the two tables using the old join syntax which uses the WHERE clause to specify the join condition. Better off use the explicit JOIN syntax using the INNER JOIN condition instead in future queries.

For more information:

Upvotes: 5

christianhs
christianhs

Reputation: 81

How about using LEFT JOIN? I think it will show the post although it does not have image.

SELECT * FROM post p LEFT JOIN post_image pi ON p.id = pi.postId

Upvotes: 1

Related Questions