CODEWITHSUNDEEP
sql-serverjoinnotin
ruisadias
ruisadias

Reputation: 17

sql server join 3 tables, not in other column of third table

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?

Upvotes: 0

Views: 815

Answers (1)

Ric .Net
Ric .Net

Reputation: 5540

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.

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.

Upvotes: 1

Related Questions