Mysql select distinct multiple columns loop through results

Question

I'm trying to select categories without duplicates from 2 different columns, the table looks something like this:

cuisine_1    cuisine_2
----------------------
italian       french
italian       chinese
japanese      german
western
french        german
international
western       sushi
sushi         steak
steak
chinese
vietnamese    chinese

Expected result is every category only once where it doesn't matter if it came from cuisine_1 or cuisine_2

I tried like so:

$categories=mysqli_query($link,"select distinct cuisine_1,cuisine_2 AS cuisine_unique from table");


while($row_cu=mysqli_fetch_array($categories)){
       echo $row_cu['cuisine_unique']."<br>";
    }

Without any luck so far. Do I have to group the results first, or what am I doing wrong ?

tried to put these two in a single array und use `array_unique()` on it? — UnskilledFreak, Nov 12 '14 at 18:38
possible duplicate of [MySQL: Get unique values across multiple columns in alphabetical order](http://stackoverflow.com/questions/2800980/mysql-get-unique-values-across-multiple-columns-in-alphabetical-order) — Paul, Nov 12 '14 at 18:42
You probably want a union as MamaWalter is suggesting. Distinct will get you distinct rows, not columns. This means that a single column may repeat because all columns in a row have to be unique for the row to be unique which is what you are seeing here. The combination of column 1 and column 2 is unique. — Jonathan Kuhn, Nov 12 '14 at 18:46

MamaWalter · Accepted Answer · 2014-11-12T18:55:23.537

2

You can try with UNION:

(SELECT distinct cuisine_1 AS cuisine_unique FROM table) UNION (SELECT distinct cuisine_2 FROM table)

SqlFiddle

edited Nov 12 '14 at 18:55

answered Nov 12 '14 at 18:43

MamaWalter

2,073
1
18
27

score 0 · Answer 2 · answered Nov 12 '14 at 18:42

0

The problem is if you filter by cuisine_1 you'll be losing some data from the other column, so you can try by getting one SELECT DISTINCT for each column and then merge both arrays and filter by array_unique

answered Nov 12 '14 at 18:42

Gerardo Charles Rojas Vega

633
7
16

Mysql select distinct multiple columns loop through results

2 Answers2