SELECT distinct two columns to one long row? 1

[Bohiti]

Example of table named "Servers":

ServerName     Admin1     Admin2
----------     ------     ------
SERVER1        Mike       Joe
SERVER2        John       Kevin
SERVER3        Mike       John
SERVER4        Joe        Stacy

I'd like to query for all (distinct) Admins. The results would look something like:

Admin
-----
Joe
John
Kevin
Mike
Stacy

This has been bugging me for some time. I realize I could use a second table to store names, but this is a really simple system, and I'd like to avoid that if possible. Can you come up with a SQL query to pull this off? Thanks very much!

 

[lespaul]

SELECT DISTINCT ADMIN1 AS "Admin" FROM SERVERS
UNION
SELECT DISTINCT ADMIN2 FROM SERVERS
ORDER BY ADMIN

 

[Crowley16]

lespaul,

wouldn't that introduce double entries if admin exsists both in admin1 and admin2?



--------------------
Procrastinate Now!

 

[Bohiti]

Thank you lespaul!
Crowley, it doesn't show duplicate fields. However, I had to remove the "order by" clause, it was saying that "Admin" isn't a selected field. Without the "order by" clause, it is already sorted, however. Shrug.

Thanks again, I wasn't familiar with the Union statement.

 

[Crowley16]

hmm, I thought the union statement worked differently...

--------------------
Procrastinate Now!

 

[r937]

UNION removes duplicate rows

UNION ALL doesn't

therefore, with UNION, DISTINCT is redundant

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[PHV]

SELECT Admin1 AS Admin FROM Servers
UNION SELECT Admin2 FROM Servers
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244