asked on

Getting a DISTINCT list from Multiple Tables

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

ASKER CERTIFIED SOLUTION

adathelad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

geotiger

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

tobey1

ASKER

Perfect.

tobey1

ASKER

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.

lluthien

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