CODEWITHSUNDEEP
phpmysqldatabaseselect
Marcello Storit
Marcello Storit

Reputation: 1

PHP select distinct value of different row in the same table

I have a database with two columns:

1. city : amsterdam, rome, london, amsterdam, rome, new york, ...
2. type : red, green, blue, red, green, ...

I have to select from a database the distinct value of columns and put it together.

The result must be like this:

amsterdam red, amsterdam blue, amsterdam green, rome red, rome blue, rome green, london ...

I tried with "select distinct" and with "array_unique", probably in the wrong way, but I can not get the result.

Upvotes: 0

Views: 408

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can do this as:

select city.city, color.color
from (select distinct city
      from t
     ) city cross join
     (select distinct color
      from t
     ) color

You are looking for all combinations of values from the two columns. The first subquery returns all values from the city column. The second all values from color. The cross join is the SQL mechanism for producing all combinations.

Upvotes: 2

Arnaud Le Blanc
Arnaud Le Blanc

Reputation: 99909

SELECT DISTINCT city, type
FROM table

Upvotes: 0

Related Questions