Solved

select distinct from 2 tables?

Posted on 2013-06-09
11
490 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 57
ASP.NET 5 Templates 2 65
Sql Count with Select Distinct 4 26
SQL Server stored proc 2 12
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

21 Experts available now in Live!

Get 1:1 Help Now