Fraser Fraser - 10 days ago 7
SQL Question

SQL left joining tables with ambiguous column names

I need to left join a few tables in a query where the column names are ambiguous.

ListingCategory_Listings:


ID | ListingCategoryID | ListingID | ..


SiteTree_Live:


ID | ClassName | Title | Content | ..


ListingCategory:


ID | IconID


File:


ID | ClassName | Name | Title | Filename | ..


I have the query:

SELECT * FROM ListingCategory_Listings
LEFT JOIN Listing ON ListingCategory_Listings.ListingID = Listing.ID
LEFT JOIN SiteTree_Live ON Listing.ID = SiteTree_Live.ID
LEFT JOIN ListingCategory ON ListingCategory_Listings.ListingCategoryID = ListingCategory.ID
LEFT JOIN File ON ListingCategory.IconID = File.ID


Both the listing and listing category data is stored in the SiteTree_Live table, when my records are returned, obviously, the Title, ID, Content and other ambiguous fields are returned under the heading Title, ID, Content.

I need to access both the Listing Title and The Listing Category Title and other specific information. If they had their data stored in different tables I could use select Listing.Title AS lTitle but how can I do something similar in this situation?

Answer

You need to alias the columns and give them different labels.
You should always put only the fields you need into the SELECT clause, listing each individually rather than using *.
Good practice also dictates giving each table a short alias.

SELECT L.Title Listing_Title, LC.Title ListingCategory_Title,
       ... all your other columns ...
FROM ListingCategory_Listings LCL
LEFT JOIN Listing L ON LCL.ListingID = L.ID 
LEFT JOIN SiteTree_Live STL ON L.ID = STL.ID 
LEFT JOIN ListingCategory LC ON LCL.ListingCategoryID = LC.ID 
LEFT JOIN File F ON LC.IconID = F.ID