MySQL Forums
Forum List  »  MySQL Query Browser

Alias a column that has 3 values into 3 columns
Posted by: Alan Slutsky
Date: March 21, 2006 08:33AM

(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.

Options: ReplyQuote


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.