Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a mySQL table with a town_id and 3 categories - cat1, cat2, cat3.

I need to be able to come up with a distinct list of all the categories in the table selected by town_id, not duplicated and in alphabetical order.

Thanks!

share|improve this question
add comment

1 Answer

If I understood the question right, you need something like

SELECT [id_value] as town_id, cat
FROM
(
    SELECT cat1 AS cat, id_value AS town_id FROM table1 WHERE town_id = [id_value]
    UNION
    SELECT cat2, id_value FROM table1 WHERE town_id = [id_value]
    UNION
    SELECT cat3, id_value FROM table1 WHERE town_id = [id_value]
)a

[id_value] - chosen town_id. UNION by default is distinct; result of union as far as I remember will be sorted automatically in most implementations, but you may add ORDER BY a.cat to make it clear...

Taking the OP's comment context:

SELECT town_id AS [16], cat FROM 
(
  SELECT Category_id AS cat, id_value AS town_id 
   FROM establishments AS estab1 WHERE town_id = 16 
  UNION SELECT Category2_id, id_value
   FROM establishments AS etsab2 WHERE town_id = 16  
  UNION SELECT Category3_id, id_value 
   FROM establishments AS estab3 WHERE town_id = 16
) AS a;
share|improve this answer
    
Thanks for the reply. I tried it as below: –  Jim Elliott Jul 15 '12 at 18:38
    
SELECT 16 as town_id, establishments.category_id FROM ( SELECT Category_id AS cat FROM establishments AS estab1 WHERE town_id = 16 UNION SELECT Category2_id FROM establishments AS etsab2 WHERE town_id = 16 UNION SELECT Category3_id FROM establishments AS estab3 WHERE town_id = 16 ) –  Jim Elliott Jul 15 '12 at 18:39
    
but I am being told that "every derived table must have its own alias" which it seems to have –  Jim Elliott Jul 15 '12 at 18:40
    
I've edited the answer in a way that I think fixes the problem, both in the original query and the invalid adoption the OP took. Feel free to edit it again to keep it your own. –  Aaron Bertrand Jul 15 '12 at 20:34
    
Thanks for your help and giving me your time! I really appreciate all the help. –  Jim Elliott Jul 15 '12 at 21:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.