So, i have two tables which are connected by a third one. Something like:
所以,我有两个表由第三个连接。就像是:
First Table:
+------------+--------------+--------------+------------+ | box_column | code_column | from_column | to_column | +------------+--------------+--------------+------------+ | 12345 | sdsad1 | madrid | london | +------------+--------------+--------------+------------+
Second Table:
+-------------+--------------+ | code_column | truck_column | +-------------+--------------+ | sdsad1 | truck1 | | sdsad1 | truck2 | | sdsad1 | truck3 | +-------------+--------------+
Third Table:
+--------------+-------------+-----------+ | truck_column | from_column | to_column | +--------------+-------------+-----------+ | truck1 | madrid | paris | | truck2 | paris | calais | | truck3 | calais | london | +--------------+-------------+-----------+
after having a join, just having the number of the box, is there any way i can distinct the trucks that make the last leg (third table have london in the to_column) and the others?
在加入之后,只有盒子的编号,是否有任何方法可以区分最后一条腿的卡车(第三张桌子在to_column中有伦敦)和其他?
1 个解决方案
#1
1
That is not very difficult to do, it's actually quite basic:
这不是很难,它实际上是非常基本的:
SELECT *
FROM dbo.code c
INNER JOIN dbo.jointable j on c.code = j.code
INNER JOIN dbo.truck t on j.truck = t.truck
WHERE c.box = 12345
AND c.[to] = t.[to]
Where code is your first table, jointable is your second table, and truck is your third table.
代码是您的第一个表,jointable是您的第二个表,而truck是您的第三个表。
The output of this query is:
此查询的输出是:
box code from to code truck truck from to
---------------------------------------------------------------------
12345 sdsad1 madrid london sdsad1 truck3 truck3 calais london
To get only the truck as output, replace
要仅将卡车作为输出,请更换
SELECT *
with
SELECT t.truck
Last but not least: I'm not seeing any primary keys, nor foreign keys in your model. Maybe you left it out. If not, please use keys and constraints.
最后但同样重要的是:我没有在模型中看到任何主键或外键。也许你把它留了出来。如果没有,请使用键和约束。
#1
1
That is not very difficult to do, it's actually quite basic:
这不是很难,它实际上是非常基本的:
SELECT *
FROM dbo.code c
INNER JOIN dbo.jointable j on c.code = j.code
INNER JOIN dbo.truck t on j.truck = t.truck
WHERE c.box = 12345
AND c.[to] = t.[to]
Where code is your first table, jointable is your second table, and truck is your third table.
代码是您的第一个表,jointable是您的第二个表,而truck是您的第三个表。
The output of this query is:
此查询的输出是:
box code from to code truck truck from to
---------------------------------------------------------------------
12345 sdsad1 madrid london sdsad1 truck3 truck3 calais london
To get only the truck as output, replace
要仅将卡车作为输出,请更换
SELECT *
with
SELECT t.truck
Last but not least: I'm not seeing any primary keys, nor foreign keys in your model. Maybe you left it out. If not, please use keys and constraints.
最后但同样重要的是:我没有在模型中看到任何主键或外键。也许你把它留了出来。如果没有,请使用键和约束。