Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Right join query not working

Tags:

sql

mysql

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

like image 806
Pratik Avatar asked Aug 13 '13 11:08

Pratik


People also ask

Why Left join and not right join?

The main difference between these joins is the inclusion of non-matched rows. The LEFT JOIN includes all records from the left side and matched rows from the right table, whereas RIGHT JOIN returns all rows from the right side and unmatched rows from the left table.

Why full join not working in SQL?

MySQL doesn't have syntax keyword FULL OUTER JOIN. You have to use combination of LEFT and RIGHT JOIN to obtain full joins. Show activity on this post. You're getting that error because MySQL does not support (or recognize) the FULL OUTER JOIN syntax.

Does MySQL support right join?

Different types of JOINs in MySQL MySQL supports the following types of JOIN clauses: INNER JOIN, OUTER JOIN, and CROSS JOIN. OUTER JOINs can further be divided into LEFT JOINs and RIGHT JOINs.

What does (+) mean in SQL JOINs?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key.


1 Answers

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;
like image 149
Gordon Linoff Avatar answered Oct 19 '22 19:10

Gordon Linoff