sql查询获取用户和订单数据

zdwk9cvp  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(346)

我有两个表在我的应用程序中,我需要得到如下数据
(为了方便起见,将使用简单的表结构)
表1:用户

user_id -> int
user_name -> varchar
user_location -> text

表2:订单

order_id -> int
sender_id -> int
receiver_id -> int
order_price -> decimal
created_at -> date

我需要的是得到订单价格和日期+发件人和收件人的名称和地点。我尝试了下面的查询,但它没有返回任何结果:

SELECT
    orders.price ,
    (users.adress) AS senderLocation , 
    (users.adress) AS receiverLocation FROM orders
JOIN users ON orders.sender_id = users.id and orders.receiver_id = users.id
yruzcnhs

yruzcnhs1#

你呢 JOIN 子句不能完全填充,除非 sender_id = receiver_id 在你的 orders_table ,这是非常不可能的。
你想要两个 JOIN s、 一个用于发送方,另一个用于接收方:

SELECT 
    orders.price , 
    (users1.adress) AS senderLocation , 
    (users2.adress) AS receiverLocation 
FROM orders
INNER JOIN users AS users1 ON orders.sender_id   = users1.id 
INNER JOIN users AS users2 ON orders.receiver_id = users2.id
jw5wzhpr

jw5wzhpr2#

如果你想为一个订单选择两个不同的用户,那么你应该加入 orders 带的表格 user 两张table。例如:

SELECT 
  orders.price , 
  (senders.adress) AS senderLocation , 
  (receivers.adress) AS receiverLocation FROM orders
JOIN users as senders ON orders.sender_id = senders.id 
JOIN users as receivers ON orders.receiver_id = receivers.id

相关问题