CODEWITHSUNDEEP
mysqlsql
Pratik
Pratik

Reputation: 1541

Right join query not working

I have two tables: opensalesorder and items.

I want to retrieve data from both tables based on the item_number and it's working fine with the below query. ( USING INNER JOIN )

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` 
INNER JOIN items on opensalesorder.item_number = items.ItemName 
WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po' 
GROUP BY opensalesorder.item_number

But I also want all rows from the 'items' table even if there isn't any match found for ItemName from opensalesorder and items.

But using below query seems not to be working for me.

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` 
RIGHT JOIN items on opensalesorder.item_number = items.ItemName  
WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po' 
GROUP BY opensalesorder.item_number

The right join will return a result from the right table even if no match found on left side.

Is the query right ?

Thanks

Upvotes: 7

Views: 11574

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

This is your query:

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` right join
     items
     on opensalesorder.item_number = items.ItemName  
 WHERE items.ItemType = 'Stock' and opensalesorder.status NOT LIKE 'on po'
 group by opensalesorder.item_number;

The where condition on opensalesorder is "undoing" the right join. The value NULL will cause it to fail.

The solution is to move it to the on clause:

SELECT opensalesorder.so_number,items.VendorName,opensalesorder.item_number
FROM `opensalesorder` right join
     items
     on opensalesorder.item_number = items.ItemName and
        opensalesorder.status NOT LIKE 'on po'
 WHERE items.ItemType = 'Stock'
 group by opensalesorder.item_number;

Upvotes: 20

Related Questions