Solved

select distinct from 2 tables?

Posted on 2013-06-09
11
500 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 22

Expert Comment

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

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 49

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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