current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
how are you doing? –  PiLHA May 27 '13 at 13:44
    
I'm not sure I understand. Do you mean that you have two tables or two columns? Could you add an example of what your data is and expected output? –  Joachim Isaksson May 27 '13 at 13:46
    
You're confusing the terms 'table', 'database', and (possibly) 'column'. –  Strawberry May 27 '13 at 15:25

2 Answers 2

up vote 2 down vote

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.

share|improve this answer
    
thanks for answering –  Marcello Storit May 27 '13 at 13:57
up vote 0 down vote 
SELECT DISTINCT city, type
FROM table
share|improve this answer
    
thanks for answering –  Marcello Storit May 27 '13 at 13:59

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.