需要SQL查询帮助：无法从两个表中获取所需的数据 [英] Need SQL query help: unable to fetch required data from two tables

问题描述

我有两张桌子：



Table_1

tra_id，lot_no，total，detail





表2

sup_id，tra_id，date



我需要来自两个表的数据这是：

lot_no，总计，详细信息，日期



数据将基于sup_id，如果供应商是1那么它应该显示供应商1的数据



我的尝试：



我试过这个：

SELECT lot_no，total，detail，date

FROM Table_1，Table_2

WHERE Table_1.sup_id = 2



但它显示重复记录。



lot_no总详细日期



1/1 2 apple 18/05/18

1/2 2 Orange 18/05/18

1/1 2 apple 18/05/18

1/2 2 Orange 18/05/18



我也尝试了左连接，右连接但是结果是相同的重复

i have two tables:

Table_1
tra_id, lot_no, total, detail


Table_2
sup_id, tra_id, date

I need data from both tables which is:
lot_no, total, detail, date

data will be based on sup_id like if supplier is one then it should show the data only of supplier 1

What I have tried:

I tried this:
SELECT lot_no, total, detail, date
FROM Table_1 , Table_2
WHERE Table_1.sup_id=2

But it showing duplicate records.

lot_no total detail date

1/1 2 apple 18/05/18
1/2 2 Orange 18/05/18
1/1 2 apple 18/05/18
1/2 2 Orange 18/05/18

I also tried the left join, right join but the result was same duplication

推荐答案

您必须指定 table_1 和 table_2 。在您的情况下，关系位于 table_2 的 tra_id 列中，该列是对<的主键的引用code> table_1 ：

You have to specify on which column is the relation between table_1 and table_2. In your case, the relation is on the tra_id column of table_2, which is a reference to the primary key of table_1:

SELECT
  a.tra_id
, a.lot_no
, a.total
, a.detail
, b.date
FROM
  table_1 AS a
  INNER JOIN table_2 AS b ON b.tra_id = a.tra_id

您的 WHERE 条款存在问题：您已指明 sup_id 列属于 table_2 ，但您在请求中使用它就好像它属于 table_1 。



希望这会有所帮助。

There is an issue with your WHERE clause: you indicated the sup_id column as belonging to table_2, though you re using it in your request as if it would belong to table_1.

Hope this helps.

作为快速修复，我有在两个表中添加了sup_id。

As quick fix, i have added the sup_id in both tables.

这篇关于需要SQL查询帮助：无法从两个表中获取所需的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！