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
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.
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.
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.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With