CODEWITHSUNDEEP
sqlsql-server
actualNoob
actualNoob

Reputation: 23

INNER JOIN with two of the same tables with the same fields

So, I'm making a bookings system for a bus, and the [Booking] table has some foreign IDs in them. I was trying to present data that is relevant to the user in a DataGridView when I realised that both "CollectionID" and "DestinationID", come from the [Location] table.

Essentially, the [Booking] table contains the CollectionID and the DestinationID, which both come from the [Location] Table as foreign keys. You cannot choose the same Collection and Destination twice.

I've tried inner joining and the result is an error, I've also tried aliases with the first [Location] table being named LOC1 and the second [Location] table LOC2

SELECT Booking.BookingID, Location.Name, Location.Name, FROM Booking 
INNER JOIN Location 
ON Booking.CollectionID = Location.LocationID 
INNER JOIN Location 
ON Booking.DestinationID = Location.LocationID

This results in: "The objects "Location" and "Location" in the FROM clause have the same exposed names. Use correlation names to distinguish them."

Upvotes: 1

Views: 247

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need aliases!

SELECT b.BookingID, lc.Name, ld.Name
FROM Booking b INNER JOIN
     Location lc
     ON b.CollectionID = lc.LocationID INNER JOIN
     Location ld
     ON b.DestinationID = ld.LocationID;

Upvotes: 6

Related Questions