SQL从三个相关表中选择数据 [英] SQL Select of data from three tables relationed

问题描述

我有三个表:

*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.

我想从特定的iduser(从php检索)中使用mysql获取此信息:

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)

查询应该如何?我经历过只从一个sql表检索数据，而没有从多个sql表检索数据的经验.

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

推荐答案

您将需要JOIN表，如下所示:

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)

如果要查询特定用户:

    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

这篇关于SQL从三个相关表中选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！