CODEWITHSUNDEEP
sql
Fraser
Fraser

Reputation: 14244

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?

Upvotes: 3

Views: 25217

Answers (2)

Cockroach
Cockroach

Reputation: 5

I suggest that to ALIAS a COLUMN name, the previous information may not be correct.

The proper syntax for COLUMN ALIAS is :

SELECT column_name AS alias_name
FROM table_name;

In a COLUMN ALIAS, the COLUMN alias assignment is declared beside the proper COLUMN name.

The proper syntax for TABLE ALIAS is :

SELECT column_name(s)
FROM table_name AS alias_name;

In a TABLE ALIAS, the TABLE alias is put beside the column being selected in the SELECT portion of the statement. The TABLE ALAIS assignment is made in the FROM portion of the statement.

The difference being that the alias assignment is made beside the proper name of what is desired to be ALIASED!

I set up a practice on the Fraser's provided example & it worked for me. I believe the answer provided was speaking of TABLE ALIAS, not COLUMN ALIAS.

I disagree with ALIAS usage where the TABLE or COLUMN ALIAS is so short that it complicates the readability of the code. I believe it should be short, but not so short that the code is not 'self documenting' for maintenance purposes. That is my 2¢ of thought on it!

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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

Upvotes: 19

Related Questions