distinct returning duplicate rows

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 2005 Forums

Transact-SQL (2005)

distinct returning duplicate rows

Author

Topic

Thomas L
Starting Member

2 Posts

Posted - 2010-01-06 : 20:08:03

I have a problem with a query, but can't understand why I'm getting an error. I needed to style the query to account for the fact that I needed to get only a single row to match on the join, which is why I added line
"left outer join Contact c on p.PIN = (select distinct PIN from Contact where Voyage = @Voyage and PrimaryContact = 1)".

The problem is while I used the "distinct" keyword, I get an error telling me the subquery returns more then one row. When I run the subquery alone, I don't see any duplicate records. Since I'm only requesting a single value (PIN) how can I get duplicate rows?

As a side note I had to do the distinct because some records had duplicate PrimaryContact valued, where only one should have been allowed.

Could use some help on this one.

Tom

GetMaxientDemo
@Voyage char(3)
AS

select p.pin , p.voyage + p.manifestID as UID, p.ship_email, p.LastName, p.FirstName, p.Initial, p.BirthDate, p.Gender, p.EthnicCode, p.A1Line1, p.A1Line2, p.A1City, p.A1State, p.A1Zip, p.A1Country, p.A1Phone,
p.InternationalPhone, c.LastName + ' ' + c.FirstName + ' ' + c.HomePhone + ' ' + c.Email as EmergencyContact, p.Email, p.EnrollingAs, p.Major, p.Cabin, n.sea, s.[Description]
from part p
left outer join Contact c on p.PIN = (select distinct PIN from Contact where Voyage = @Voyage and PrimaryContact = 1)
left outer join cabins n on n.cabin = p.cabin and n.voyage = p.voyage
left outer join seacode s on n.sea = s.sea
where p.Voyage = @Voyage and p.status = '3' and p.parttype = '1'
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-06 : 23:06:15

How many rows does the "select distinct" query return? It can only return one to avoid the error.

How about this instead:
select p.pin , p.voyage + p.manifestID as UID, p.ship_email, p.LastName, p.FirstName, p.Initial, p.BirthDate, p.Gender, p.EthnicCode, p.A1Line1, p.A1Line2, p.A1City, p.A1State, p.A1Zip, p.A1Country, p.A1Phone,
p.InternationalPhone, c.LastName + ' ' + c.FirstName + ' ' + c.HomePhone + ' ' + c.Email as EmergencyContact, p.Email, p.EnrollingAs, p.Major, p.Cabin, n.sea, s.[Description]
from part p
left outer join Contact c on p.PIN = c.PIN
left outer join cabins n on n.cabin = p.cabin and n.voyage = p.voyage
left outer join seacode s on n.sea = s.sea
where p.Voyage = @Voyage and p.status = '3' and p.parttype = '1' and
c.Voyage = @Voyage and c.PrimaryContact = 1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 00:41:43

it would be better if you post some sample data and explain what according to you are 'distinct' ones

Thomas L
Starting Member

2 Posts

Posted - 2010-01-07 : 09:43:45

The problem is with the Contact table. It has all of the data elements you see in the query above and the problem I have arises because in the front end editing they allowed the user to check off "PrimaryContact" more then once. My original query is the same as was posted, but if PrimaryContact is valued more then once then you get as many records returned as there are when PrimaryContact = 1 and I only want one. In MySql I could just append limit 1 to the subquery and it would be correct, that is to say it would return only the first of n records returned in the query. I tried using the "top" keyword, but can't seem to find the correct syntax to return only the first record of the Contact table records that matches the Part table record. The problem with the top keyword is it applies to the entire query and not the records returned, therefore I get only one record instead of the only the first contact record whose PIN = Part.PIN. In short if the contact table has two or more records with PrimaryContact =1 then I only want the first record, where the Part.PIN = Contact.PIN.
Hope this explains my dilemma.

Thanks,
Tom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 09:51:29

may be this
[code]
select p.pin , p.voyage + p.manifestID as UID, p.ship_email, p.LastName, p.FirstName, p.Initial, p.BirthDate, p.Gender, p.EthnicCode, p.A1Line1, p.A1Line2, p.A1City, p.A1State, p.A1Zip, p.A1Country, p.A1Phone,
p.InternationalPhone, c.LastName + ' ' + c.FirstName + ' ' + c.HomePhone + ' ' + c.Email as EmergencyContact, p.Email, p.EnrollingAs, p.Major, p.Cabin, n.sea, s.[Description]
from part p
left outer join cabins n on n.cabin = p.cabin and n.voyage = p.voyage
left outer join seacode s on n.sea = s.sea
outer apply (select top 1 * from Contact where PIN = p.PIN and Voyage = @Voyage and PrimaryContact = 1)c
where p.Voyage = @Voyage and p.status = '3' and p.parttype = '1' [code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources