CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
sridhar
sridhar

Reputation: 1389

MySQL select statement involving join

I have two tables, a and b

table a

--------------------
|id  |  item       |
--------------------
|1   |  apple      |
--------------------
|2   |  orange     |
--------------------
|3   |  mango      |
--------------------
|4   |  grapes     |
--------------------
|5   |  plum       |
--------------------
|6   |  papaya     |
--------------------
|7   |  banana     |
--------------------

table b

----------------------------
user_id | item_id | price  |
----------------------------
32      | 3       | 250    |
----------------------------
32      | 6       | 180    |
----------------------------
32      | 2       | 120    |
----------------------------

Now I want to join the two tables in MySql so that I get list of all fruits in table a along with their prices as in table b for user 32; something like this:

-----------------------------
|id  |  item       |  price  |
-----------------------------
|1   |  apple      |         |
-----------------------------
|2   |  orange     |   120   |
------------------------------
|3   |  mango      |   250   |
------------------------------
|4   |  grapes     |         |
------------------------------
|5   |  plum       |         |
------------------------------
|6   |  papaya     |   180   |
------------------------------
|7   |  banana     |         |
------------------------------

The best I could do was this: 

SELECT      a.id, 
            a.item, 
            b.price 
FROM        a 
INNER JOIN  b   ON  a.id = b.item_id 
WHERE       b.user_id = 32

This gives me only the rows whose price have been set, not the ones whose prices have not been set. How do I frame the SQL?

Upvotes: 3

Views: 60

Answers (3)

tylermoseley
tylermoseley

Reputation: 122

You need a "LEFT" Join to ensure that all records from the left side are returned regardless of a corresponding value on the right side. 

SELECT a.id, a.item, b.price
FROM a
LEFT JOIN b ON a.id = b.item_id
WHERE b.user_id = 32

Upvotes: -1

Christian
Christian

Reputation: 888

You have to use a OUTER JOIN instead

SELECT a.id, a.item, b.price 
FROM a LEFT OUTER JOIN b ON a.id = b.item_id WHERE b.user_id = 32

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29071

Use LEFT OUTER JOIN instead of INNER JOIN

Try this: 

SELECT a.id, a.item, b.price 
FROM a 
LEFT OUTER JOIN b ON a.id = b.item_id AND b.user_id = 32;

Upvotes: 4

Related Questions