values of two columns as a list

[october21]

I have a table with two colums, what SELECT code allows me to join the values in a long list (with duplicates removed) ?

for example :
leveltwo levelthree

Caribbean Bermuda
Caribbean Bermuda
Cuba Varadero
Indian Ocean Seychelles

should give
Bermuda,Caribbean,Cuba,Indian Ocean,Seychelles,Varadero

Thank you
Shaun

[i6fpwonder]

I don't think I understand what U R trying to do clearly, but have you tried

SELECT DISTINCT COL1, COL2 FROM MyTable

US, MN
US, MS

As U can C, with above example, one could get the US entry multiple times, if that is a problem the below entry should fix it, although I will admit it is VERY INNEFICIENT.


SELECT DISTINCT COL1 FROM MyTable
UNION
SELECT DISTINCT COL2 FROM MyTable WHERE COL2 NOT IN ( SELECT DISTINCT COL1 FROM MyTable)


Now this should return US,MN,MS


Hope this helps

[october21]

<%
Set objRS = Server.CreateObject("ADODB.Recordset")
sqlQuery="SELECT DISTINCT leveltwo FROM relation UNION SELECT DISTINCT levelthree FROM relation WHERE levelthree NOT IN ( SELECT DISTINCT leveltwo FROM relation)"

objRS.Open sqlQuery, objConn, 3, 3
do while NOT objRS.EOF
Response.Write "<option value'" & objRS("leveltwo") & "'>" & objRS("leveltwo") & "</option>"
objRS.MoveNext
loop
objRS.close
set objRS=nothing
%>

Which gives me the required list, well done, shame you need to select twice effectively .

Best wishes,
Shaun