CODEWITHSUNDEEP
mysqldump
NVG
NVG

Reputation: 3313

MySQL Join to show all rows

I have two MySQL tables with the following structure:

TABLE `orders`

order_id
order_date
product_id

TABLE `products`

product_id
product_name
product_price
product_type

I want to show all rows from table orders and if there is a product_id in TABLE products show all data of that product. If there isn't, show all data from orders table only.

I tried this: 

SELECT * FROM orders AS o RIGHT JOIN products AS p ON o.product_id=p.product_id

But if we have a row with a product_id inside orders table that doesn't exist in products table, then the row from orders is not showed. I want the row from orders id to show no matter if there is a product_id inside products table. If there is JOIN it, if it isnt show just the data from table orders.

Any suggestions?

Upvotes: 0

Views: 1395

Answers (1)

user20232359723568423357842364
user20232359723568423357842364

Reputation: 4634

You should be doing a LEFT JOIN

With a left join, all rows from ORDERS will be returned. The columns from the products table will be null if the criteria for the join is not met.

SELECT    o.*,p.* 
FROM      orders AS o 
LEFT JOIN products AS p 
       ON o.product_id=p.product_id`

See the MySQL manual join syntax

Upvotes: 4

Related Questions