?
Solved

select distinct from 2 tables?

Posted on 2013-06-09
11
Medium Priority
?
509 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 93

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

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.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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