0

I'm facing a strange behavior that I must be interpreting wrong, Given that computers always execute exactly what you tell them to, I must be telling it wrong :)

Use Case :
I have a table called orders_details that consists of columns of interest, both foreign keys, order_id and product_id.

I also have a table called orders which has a PK of order_id and a table called product_details that has a PK of product_id column.

The requirement :

Select all orders with their respective order detail and product detail.

What I tried : 

SELECT ord.shipping_ids, ord.order_id, ord.firstname, ord.lastname, ord.order_id,
ord.payment_id, det.product_id, ord.timestamp, prd.product

FROM  `cscart_orders` AS ord  

LEFT JOIN cscart_order_details AS det ON ord.order_id = det.order_id

LEFT JOIN cscart_product_details AS prd ON prd.product_id = det.product_id

Now this results is getting null for all the product_details columns, so I don't get what I need.

If i replace the 2nd join to an inner join, I get corrupted data (multiple copies of each row without any logical order that I've noticed).

So, What am I missing? Isn't this how joins should work?

Edit :

http://sqlfiddle.com/#!2/f98463/2 -sqlfiddle, Never knew a fiddle exists for sql, sorry for not posting it in the first place.

2nd edit :

my DB tables had data integrity issues :( someone didn't properly set FK's and PK's, My original query does indeed work on a sample data, Thanks for pointing me to the right place to double check myself

Patrick
  • 3,289
  • 2
  • 18
  • 31

1 Answers1

0

Your given query working fine on sql fiddle If you give expected output so we can help you.

SELECT ord.shipping_ids, ord.order_id, ord.firstname, ord.lastname, ord.order_id,
ord.payment_id, det.product_id, ord.timestamp, prd.product

FROM  `cscart_orders` AS ord  

LEFT JOIN cscart_order_details AS det ON ord.order_id = det.order_id

LEFT JOIN cscart_product_details AS prd ON prd.product_id = det.product_id
Sadikhasan
  • 18,365
  • 21
  • 80
  • 122