[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

select distinct from 2 tables?

Posted on 2013-06-09
11
Medium Priority
?
507 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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