CODEWITHSUNDEEP
mysqlsqlselectjoin
Pablo De Luca
Pablo De Luca

Reputation: 823

SQL Select of data from three tables relationed

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.

Upvotes: 1

Views: 142

Answers (1)

Khalid Amin
Khalid Amin

Reputation: 902

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

Upvotes: 2

Related Questions