-
values of two columns as a list
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
-
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
-
<%
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|