[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
5
Medium Priority
?
300 Views
Last Modified: 2010-03-19
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
Comment
Question by:tobey1
5 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 150 total points
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

by:geotiger
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

by:tobey1
ID: 13967486
Perfect.
0
 
LVL 5

Author Comment

by:tobey1
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

by:lluthien
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question