CODEWITHSUNDEEP
sqlms-accessleft-join
dilbert
dilbert

Reputation: 3098

Select query - Joining same table multiple times to different columns

I'm a SQL novice trying to write a select query that involves a join of multiple tables, with one table multiple times.

The relationships are shown below: enter image description here

The output of the query I'm trying to achieve is this:

Truck_ID|Pushbutton_ID|Trailer01_ID|Trailer02_ID|Trailer03_ID|Trailer04_ID|Trailer05_ID

The joining of the tables Trucks and Pushbutton_Assignments is straightforward. The difficulty is joining the Trailer_Assignment table multiple times.

The structure of Trailer_Assignments is:

Trailer_ID|Truck_ID|Order

The Trailer_ID and Truck_ID fields have foreign key relationships with other tables. The TrailerXX_ID fields of the query are related to the Order field of the Trailer_Assignment table; all records with Order == 1 have Trailer_ID put in Trailer01_ID, records with Order == 2 to Trailer02_ID and so on.

I based my query from here. It works for a single column but Access (I think) doesn't like subsequent columns.

Working Query code (single column):

SELECT Trucks.ID, Pushbutton_Assignment.Pushbutton_ID, Trailer_Assignment.Trailer_ID AS Trailer01_ID, Trailer_Assignment.Trailer_ID AS Trailer02_ID, Trailer_Assignment.Trailer_ID AS Trailer03_ID, Trailer_Assignment.Trailer_ID AS Trailer04_ID, Trailer_Assignment.Trailer_ID AS Trailer05_ID FROM (Trucks LEFT JOIN Pushbutton_Assignment ON Trucks.ID = Pushbutton_Assignment.Truck_ID) LEFT JOIN Trailer_Assignment ON Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 1;

EDIT:

Non-working Query code (two column):

SELECT Trucks.ID, Pushbutton_Assignment.Pushbutton_ID, Trailer_Assignment.Trailer_ID AS Trailer01_ID, Trailer_Assignment.Trailer_ID AS Trailer02_ID, Trailer_Assignment.Trailer_ID AS Trailer03_ID, Trailer_Assignment.Trailer_ID AS Trailer04_ID, Trailer_Assignment.Trailer_ID AS Trailer05_ID (((FROM (Trucks LEFT JOIN Pushbutton_Assignment ON Trucks.ID = Pushbutton_Assignment.Truck_ID)) LEFT JOIN Trailer_Assignment ON (Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 1)) LEFT JOIN Trailer_Assignment ON (Trucks.ID = Trailer_Assignment.Truck_ID AND Trailer_Assignment.Order = 2));

This is using Access 2003.

Upvotes: 1

Views: 1714

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

MS Access has all sorts of weird restrictions, such as difficulty handling constants in LEFT JOIN conditions. So, just surrender and use correlated subqueries . . . you only want one value anyway:

select t.id, pba.pushbutton_id,
       (select top 1 ta.trailer_id from Trailer_Assignment as ta where ta.Truck_id = t.id and ta.order = 1
       ) as Trailer01_id,
       (select top 1 ta.trailer_id from Trailer_Assignment as ta where ta.Truck_id = t.id and ta.order = 2
       ) as Trailer02_id,
       (select top 1 ta.trailer_id from Trailer_Assignment as ta where ta.Truck_id = t.id and ta.order = 3
       ) as Trailer03_id,
       (select top 1 ta.trailer_id from Trailer_Assignment as ta where ta.Truck_id = t.id and ta.order = 4
       ) as Trailer04_id,
       (select top 1 ta.trailer_id from Trailer_Assignment as ta where ta.Truck_id = t.id and ta.order = 5
       ) as Trailer05_id
from Trucks as t LEFT JOIN
     Pushbutton_Assignment as pba
     on t.id = pba.truck_id;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

SELECT Trucks.ID, 
   Pushbutton_Assignment.Pushbutton_ID,
   iif(ta.order = 1, ta.Trailer_ID, NULL) AS Trailer01_ID,
   iif(ta.order = 2, ta.Trailer_ID, NULL) AS Trailer02_ID,
   iif(ta.order = 3, ta.Trailer_ID, NULL) AS Trailer03_ID,
   iif(ta.order = 4, ta.Trailer_ID, NULL) AS Trailer04_ID,
   iif(ta.order = 5, ta.Trailer_ID, NULL) AS Trailer05_ID
FROM Trucks LEFT JOIN Pushbutton_Assignment 
ON Trucks.ID = Pushbutton_Assignment.Truck_ID 
LEFT JOIN Trailer_Assignment ta ON Trucks.ID = ta.Truck_ID

This is one more way to do it with if statements. You would need to refer to the table only once. Add a where clause if needed, to only look for orders 1,2,3,4 and 5.

Upvotes: 0

Bulat
Bulat

Reputation: 6979

As you are using the same table several times in your query, you need to provide an alias for each one:

SELECT Trucks.ID, 
   Pushbutton_Assignment.Pushbutton_ID,
   ta1.Trailer_ID AS Trailer01_ID,
   ta2.Trailer_ID AS Trailer02_ID,
   ta3.Trailer_ID AS Trailer03_ID,
   ta4.Trailer_ID AS Trailer04_ID,
   ta5.Trailer_ID AS Trailer05_ID
FROM (Trucks LEFT JOIN Pushbutton_Assignment ON Trucks.ID = Pushbutton_Assignment.Truck_ID) 
    LEFT JOIN Trailer_Assignment ta1 ON Trucks.ID = ta1.Truck_ID AND ta1.Order = 1
    LEFT JOIN Trailer_Assignment ta2 ON Trucks.ID = ta2.Truck_ID AND ta2.Order = 2
    LEFT JOIN Trailer_Assignment ta3 ON Trucks.ID = ta3.Truck_ID AND ta3.Order = 3
    LEFT JOIN Trailer_Assignment ta4 ON Trucks.ID = ta4.Truck_ID AND ta4.Order = 4
    LEFT JOIN Trailer_Assignment ta5 ON Trucks.ID = ta5.Truck_ID AND ta5.Order = 5;

Upvotes: 0

Related Questions