0

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
NVG
  • 3,248
  • 10
  • 40
  • 60

1 Answers1

3

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

user20232359723568423357842364
  • 4,624
  • 1
  • 18
  • 28