Solved

# Getting a DISTINCT list from Multiple Tables

Posted on 2005-05-10
246 Views
I have a query that I need to run were I need to pull 3 columns from 3 different tables, and display them as 1 column.  I am looking for the most efficient way to do this.  Any Ideas?  Here is what I was thinking, but it seems to run forever.

Select Distinct Table1.col1, Table2.col1, Table3.col1
FROM Table1, Table2, Table3
ORDER BY col1
0
Question by:tobey1

LVL 23

Accepted Solution

Hi,

Try this:

SELECT DISTINCT Col1 FROM Table1
UNION
SELECT DISTINCT Col1 FROM Table2
UNION
SELECT DISTINCT Col1 FROM Table3
0

LVL 12

Expert Comment

without specifying how the records are joined, you are creating Cartisan product here. You should have a where clause (join clause):

Select Distinct Table1.col1, Table2.col1, Table3.col1
FROM Table1, Table2, Table3
WHERE table1.id = table2.id and table1.id=table3.id
ORDER BY col1
0

LVL 5

Author Comment

Perfect.
0

LVL 5

Author Comment

The Data in Table1, Table2 and Table3 do not overlap 100%, I need all the values.  I think that adathelad solution is giving me what I need based ont he results that I got.
0

LVL 11

Expert Comment

this:

"Select Distinct Table1.col1, Table2.col1, Table3.col1
FROM Table1, Table2, Table3
ORDER BY col1"

will give you the so coalled "cartesian product", because it is lacking a "where-clause"
of all these three tables, meaning that EVERY Record in table1 is conneccted to EVERY recordin table2 and all of those records are connected to EVERY record in table 3...

that is a lot of records.
hence it takes for ever.

the solution provided by adathelad gives you one column with all the data in it,
although you won't be able to see where each column came from,
should that be needed.

just to clarify some things.
cheers
0

## Featured Post

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.