Select without duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select without duplicates

Author

Topic

vipetrul
Starting Member

2 Posts

Posted - 2008-07-09 : 14:49:52

Hello,

I'm having a problem creating such Select statement:

I have 2 hypothetical tables:

Contacts
---------------
contact_id (PK)
contact_name

Locations
--------------
location_state(PK)
location_city (PK)
location_contact_id (FK)

One contact can have many locations assigned to it.

I want to select contact_id, contact_name, location_state, location_city FROM Contacts, Locations WHERE location_state='NY' AND contact_id=location_contact_id

But, I want to get only one record per contact even if there are many locations assigned to this contact (Several cities in state 'NY'). It doesn't matter which city would be included in the result.

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 15:27:42

This would work if you truly don't care which state/city you choose.
create table #Contacts(contactid,contactname,locationstate,locationcity)

insert into #contacts(contactid,contactname) select contactid,contactname from contacts

update #contacts
set locationstate=l.locationstate,
locationcity=l.locationcity
from locations l
inner join #contacts c on l.location_contact_id=c.contactid

Mike
"oh, that monkey is going to pay"

vipetrul
Starting Member

2 Posts

Posted - 2008-07-09 : 15:42:48

Thank you for quick response. But I think I wasn't clear enough...

The data in tables already exists, and select statements are being ran on demand by ASP.NET application.

And, if it is possible, I would like to avoid using temporal table and only use SELECT statements with help of views and functions (if needed).

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-10 : 01:29:39

[code]select c.contact_id, c.contact_name, l.location_state, l.location_city
FROM Contacts c
INNER JOIN Locations l
ON c.contact_id=l.location_contact_id
INNER JOIN (SELECT MAX(location_city) AS latest,location_contact_id
FROM Locations
WHERE location_state='NY'
GROUP BY location_contact_id)t
ON t.location_contact_id=l.location_contact_id
AND t.latest=l.location_city
WHERE l.location_state='NY' [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources