Right join query not working

mysql, sql

Solution

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;

Problem

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

Original source