CODEWITHSUNDEEP
mysqlsql
Shahid Sarwar
Shahid Sarwar

Reputation: 1209

mysql LEFT JOIN: join table if value exists in column or if value does not exist use 'null'

I am using this query to join to return user_id and prod_id. If prod_id exists than the result should print with the user_id and the prod_id. However, if the prod_id does not exist null should be printed. But when I use the below query it only prints the two columns if values exists for both user_id and prod_id if prod_id does not exist it returns blank. Please help

SELECT DISTINCT A.wishlist_id, B.prod_id
FROM  wp_yith_wcwl A
LEFT JOIN  wp_yith_wcwl B ON A.user_id=B.user_id
WHERE A.user_id =5
AND B.prod_id=101424

Upvotes: 0

Views: 1468

Answers (1)

sgeddes
sgeddes

Reputation: 62841

You are negating your outer join. You need to move the where criteria to the on of the join:

SELECT DISTINCT A.wishlist_id, B.prod_id
FROM  wp_yith_wcwl A
    LEFT JOIN  wp_yith_wcwl B ON A.user_id=B.user_id
        AND B.prod_id=101424 
WHERE A.user_id =5

Upvotes: 2

Related Questions