[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Getting a DISTINCT list from Multiple Tables

Posted on 2005-05-10
Medium Priority
300 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

ID: 13967469
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

ID: 13967482
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

ID: 13967486
Perfect.
0

LVL 5

Author Comment

ID: 13967496
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

ID: 13967508
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month17 days, 21 hours left to enroll