时间戳

首页 > 解决方案 > 从多个表中检索数据不起作用

sql - 从多个表中检索数据不起作用

问题描述

我有三张桌子

quiz:              activetruck:
id                 id   
name               name
origin             origin
destination        destination
total_trucks       total_trucks
material_type      scheduled_date
scheduled_date     offered_price
offered_price      owner_id
owner_id           subject_id
subject_id

我运行此查询以从两个表中提取公共数据

Select * from quiz as cq, activetruck as ca
where cq.origin=ca.origin and cq.destination=ca.destination and 
cq.subject_id=ca.subject_id and cq.total_trucks<=ca.total_trucks;

有第三个表供应商，供应商通过owner_id连接到活动卡车

columns of suppliers are:
user_id
supplier_name
supplier_company_name
supplier_email
supplier_gst
supplier_pan
supplier_address
supplier_origin
supplier_service

我想获取 activetruck.owner_id = supplier.user_id 的供应商的所有详细信息我尝试运行它但它没有用

Select * from classroom_quiz as cq, classroom_activetruck as ca, classroom_supplier as cs 
where cq.origin=ca.origin and cq.destination=ca.destination and
 cq.subject_id=ca.subject_id and cq.total_trucks<=ca.total_trucks and
 ca.owner_id=cs.user_id;

标签： sql

解决方案

左外连接是您需要使用的。更多信息在这里。

activetruck 和供应商的左外连接将返回匹配的供应商详细信息，但在不匹配的情况下，它仍将返回 activetruck 行。您的查询正在加入 activetruck 匹配供应商的位置，但如果没有匹配，则它不会返回任何内容。

SELECT *
FROM classroom_quiz AS cq
INNER JOIN classroom_activetruck ca ON cq.origin = ca.origin
    AND cq.destination = ca.destination
    AND cq.subject_id = ca.subject_id
    AND cq.total_trucks <= ca.total_trucks
LEFT OUTER JOIN classroom_supplier cs ON ca.owner_id = cs.user_id;

推荐阅读