Alias a column that has 3 values into 3 columns
(This same question was previously posted with a different Subject. I hope this new Subject is more effective at getting a response).
I have a column that contains various values in the rows, and I am trying to extract 3 values as their own column. See the following SQL:
SELECT DISTINCT c1.Name AS Person, c2.Name AS Company, c3.Name AS Product, d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1 USING (CanonicalID)
LEFT JOIN Document d USING(DocumentID)
LEFT JOIN Entity e2 USING (DocumentID)
LEFT JOIN Canonical c2 USING (CanonicalID)
LEFT JOIN Canonical c3 USING (CanonicalID)
WHERE c1.CategoryID=9 AND c2.CategoryID=2 and c3.CategoryID=10
The query above returns an empty set. If I change the query to contain only 2 values (c1 and c2, c1 and c3, or c2 and c3) rows are returned.
SELECT DISTINCT c1.Name AS Person, c2.Name AS Company, d.title, d.identifier
FROM Canonical c1
LEFT JOIN Entity e1 USING (CanonicalID)
LEFT JOIN Document d USING(DocumentID)
LEFT JOIN Entity e2 USING (DocumentID)
LEFT JOIN Canonical c2 USING (CanonicalID)
WHERE c1.CategoryID=9 AND c2.CategoryID=2
Does anyone know of a workaround for this?
I am running MySQL version 4.1.14 on Windows XP.
Thanks.
Subject
Written By
Posted
Alias a column that has 3 values into 3 columns
March 21, 2006 08:33AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.