Want to select distinct from 3 fields

Question

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!

Answer 1

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;