I have a column ContentID in a table that identifies content that exists in other tables. For example, it could refer to a pageID, productID, etc. My plan is to use that to pull through other information I need, such as a page name or product name. This is what I have so far:
SELECT TL.ID, TL.TableName, TL.FileName, TL.ContentID, p.PageName AS Content
FROM TranslationLog TL
LEFT JOIN Pages P ON TL.ContentID = P.PageID
LEFT JOIN Categories C ON TL.ContentID = C.CategoryID
LEFT JOIN ProductDescriptions PD ON TL.ContentID = PD.SKU
The idea is for each row, I want to get the data for the specified content using the TableName and ContentID fields. Currently, I'm able to get PageName by selecting p.PageName AS Content. However, I'd like to do this for each of the tables; if the row corresponds to the pages table, then query that table - same for categories and product descriptions. I also need it to have the alias "Content", regardless of which field from another table we're using, such as PageName or ProductName.
Is it possible to do this?
EDIT:
The solution posted by rd_nielsen was almost perfect, but it turned out there was actually a bit of overlap with the ContentID. Here's what I ended up with to fix it:
SELECT TL.ID, TL.TableName, TL.FileName, TL.ContentID, coalesce(P.PageName, C.CategoryName, PD.ProductName)
FROM TranslationLog TL
LEFT JOIN Pages P ON TL.ContentID = P.PageID AND TL.TableName = 'Pages'
LEFT JOIN Categories C ON TL.ContentID = C.CategoryID AND TL.TableName = 'Categories'
LEFT JOIN ProductDescriptions PD ON TL.ContentID = PD.SKU AND TL.TableName = 'Products'
If the values of TranslationLog.ContentID can appear in only one of the related tables, then you can coalesce the values from those tables:
SELECT
TL.ID,
TL.TableName,
TL.FileName,
TL.ContentID,
coalesce(p.PageName, C.CategoryName, PD.ProductName) AS Content
FROM
...
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments