SQL Select of data from three tables relationed

Question

I Have three tables:

*orders:

  -id (PK)

  -iduser (FK)

  -date



*detail_orders:

  -id(PK)

  -or_id (FK of id on orders)

  -prod_id (FK of id on products)

  -price

  -quantity


*products:

  -id (PK)

  -description

There is a table of orders, another of the detail (the products, prices, quianties) of the order and another to retrieve the description of the product.

I want to get this using mysql from a specific iduser (retrieved from php):

order.id | order.date:

products.description | orders.quantity | orders.price
products.description | orders.quantity | orders.price
products.description | orders.quantity | orders.price

... (etc while there are products on this order)

How should be the query? I've experience retrieving data from only one sql table and no joining data from more than one.

SO is not a tutoring service. You need to do your own studying to learn the basics of SQL joins. — Barmar
– Barmar, Commented Feb 18, 2016 at 0:46
@Barmar Sory, I forgot to write that I was reading about JOIN and I understand the concept, but when there are more than two tables envolved I couldn't do it. — Pablo De Luca
– Pablo De Luca, Commented Feb 18, 2016 at 0:54
Show what you tried, and then we can help you understand what you did wrong. There's no significant difference with 3 tables. — Barmar
– Barmar, Commented Feb 18, 2016 at 0:54

Khalid Amin · Accepted Answer · 2016-02-18 00:49:30Z

2

You will need to JOIN the tables, like this:

SELECT products.description, orders.quantity, orders.price
FROM detail_orders
  INNER JOIN products ON (products.prod_id = detail_orders.prod_id)
  INNER JOIN orders ON (orders.id = detail_orders.or_id)

If you want to query for a specific user:

    SELECT products.description, orders.quantity, orders.price
    FROM detail_orders
      INNER JOIN products ON (products.prod_id = detail_orders.prod_id)
      INNER JOIN orders ON (orders.id = detail_orders.or_id)
    WHERE orders.iduser = someUser

answered Feb 18, 2016 at 0:49

Khalid Amin

9124 gold badges12 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo De Luca Over a year ago

It was useful to solve my problem. Thanks you very much.

Collectives™ on Stack Overflow

SQL Select of data from three tables relationed

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related