I have a table wich has links in it. So amongst others, there are 2 columns that contain URLs; Source and Destination. I need to get a list of all the unique urls that are present in the table (to create a lookup table), but a url can be in both columns of course. Using distinct on the 2 columns seems to return unique combinations, rather than single values. Can I do this in 1 query or should I use 2 (one for each column) and then a 3d distinct select on that result?
2 Answers
You can use an UNION, which will remove the duplicates (while UNION ALL will keep the duplicates)
SELECT FirstUrlColumn AS url
FROM myTable
UNION
SELECT secondUrlColumn AS url
FROM myTable
5 Comments
Chrisvdberge
Any suggestion on a way to check the output? It is giving me the exact same number of rows as the distinct query for the 2nd column. This could be correct of course, but like to be sure.
Raphaël Althaus
Well, if you don't trust your RDBMS, this is a problem ;) You can check manually on a small part of your data, but, UNION does... what it has to do ! Maybe show your query if you select more fields than just url.
Raphaël Althaus
@Chrisvdberge you may try with an UNION ALL and see if you find differences, but...
Chrisvdberge
thx.. I trust it does what I need it to do ;) Any suggestion on the follow up question; how to get the unique combinations from the 1st table using the ID's of the second? I guess this will be a Inner Join?
Raphaël Althaus
@Chrisvdberge your follow up is not that clear. Can you elaborate or ask a new question please ?
Try the following:
SELECT DISTINCT url
FROM (
SELECT col1 AS url
FROM TABLE
UNION
SELECT col2 AS url
FROM TABLE
) urls
1 Comment
Raphaël Althaus
no need to select distinct on the union query. Union will remove the duplicates !
GROUP BY.