Solved

# select distinct from 2 tables?

Posted on 2013-06-09
487 Views
I have 2 tables:  dbo.populations   dbo.postalcodes

Each table has a column called City, State and each table may have duplicate values of those.

What I want to do is select all distinct City where State = "X" from both tables, and have the results be distinct results.

So for example:

Dbo.Populations
-----------------------------
Cheyanne, WY
Cheyanne, WY
Capser, WY

Dbo.PostalCodes
-------------------
Cheyanne, WY
Laramie, WY

The result should be:
Cheyanne, WY
Casper, WY
Laramie, WY

Notice that both tables had Cheyanne, WY, and one of them even had it twice, but I'm only getting it one time.

Thanks!!
0
Question by:arthurh88
• 3
• 2
• 2
• +4

LVL 22

Accepted Solution

Thomasian earned 500 total points
ID: 39232643
``````SELECT City FROM dbo.populations WHERE State = 'X'
UNION
SELECT City FROM dbo.postalcodes WHERE State = 'X'
``````
0

LVL 48

Expert Comment

ID: 39232646
select distinct
city, state
from Dbo.PostalCodes pc
left join Dbo.Populations  pop on pc.state = pop.state and pc.city = pop.city

-- this assumes postalcodes has 'all' but populations might not

select
city, state, count(pc.id) as pc_count, count(pop.id) as pop_count
from Dbo.PostalCodes pc
left join Dbo.Populations  pop on pc.state = pop.state and pc.city = pop.city
group by
city, state

is an alternative that would also indicate how many of each is involved
0

Author Comment

ID: 39232647
both tables have cities, states that are not in the other one.  That is why I want to select from both so that I can get them all.  but they also both have rows that contain duplicate city, state, and I only want to get the distinct city for any given state between both of them.
0

LVL 22

Expert Comment

ID: 39232653
Using union will remove all duplicate records
0

LVL 48

Expert Comment

ID: 39232681
union will resolve that problem, a full outer join would an alternate but certainly no better than the union

union will also produce a "distinct" (unique row) result.

Thomasian's suggestion is a good one.
0

LVL 4

Expert Comment

ID: 39232791
T-SQL 101
``````With qryPopulations ( City, State ) As (
Select Distinct City, State
From   Populations
), qryPostalCodes ( City, State ) As (
Select Distinct City, State
From   PostalCodes
), Cities ( City, State ) As (
Select Coalesce( qryPopulations.City, qryPostalCodes.City )
, Coalesce( qryPopulations.State, qryPostalCodes.State )
From   qryPopulations Full Join qryPostalCodes
On    qryPopulations.City = qryPostalCodes.City
And   qryPopulations.State = qryPostalCodes.State
)
Select City
From   Cities
Where  State = 'X'
``````
Complex but it works!!!
0

LVL 92

Expert Comment

ID: 39234784

Make sure that that is not how you have your actual data.  That city name is spelled "Cheyenne".

:)

0

LVL 75

Expert Comment

ID: 39236645
Not to mention "Capser, WY"

And yes, no points please.  Not even with a "C" grade. :)
0

LVL 48

Expert Comment

ID: 39236663
isn't Casper, WY a ghost town? sorry - couldn't resist
0

LVL 4

Expert Comment

ID: 39237057
isn't Casper, WY a ghost town? sorry - couldn't resist

LOL
0

LVL 21

Expert Comment

ID: 39237362
SElect Distinct City  FROM (
SELECT City FROM dbo.populations WHERE State = 'X'
UNION
SELECT City FROM dbo.postalcodes WHERE State = 'X') A
0

## Featured Post

### Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.â€‹
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.