Solved

select distinct from 2 tables?

Posted on 2013-06-09
11
493 Views
Last Modified: 2013-07-04
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
Comment
Question by:arthurh88
  • 3
  • 2
  • 2
  • +4
11 Comments
 
LVL 22

Accepted Solution

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

Open in new window

0
 
LVL 48

Expert Comment

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

by:arthurh88
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 22

Expert Comment

by:Thomasian
ID: 39232653
Using union will remove all duplicate records
0
 
LVL 48

Expert Comment

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

by:BAKADY
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'

Open in new window

Complex but it works!!!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39234784
<no points please>

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

:)

</no points please>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39236645
Not to mention "Capser, WY"

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

Expert Comment

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

Expert Comment

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

LOL
0
 
LVL 21

Expert Comment

by:Alpesh Patel
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how the fundamental information of how to create a table.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now