Repeated Sql Returns

[quinnipiac0807]

This is my statement and it returns 8 of each each answer even though there should only be 2. So there's 16 instead of 2. I know you can use distinct but is there anyother way to fix this. I feel like select distinct will leave some answers out.

select t.AssetID, a.AssetID, t.CustodianID, a.AssetTag,
a.IsActive, a.AssetDescID, a.AssetMfrID, a.AssetModelID,
a.SiteID, d.AssetDescID, d.AssetDesc, f.AssetMfrID,
o.AssetModelID, s.SiteID, f.MfrDesc, o.AssetModelDesc,
s.SiteName, a.IsBroken, c.CustodianID, c.ITDivisionID,
i.ITDivisionID, i.ITDivShortName
from psd_Asset a
left join psd_AssetCustodian t on a.AssetID = t.AssetID
left join psd_AssetDesc d on a.AssetDescID = d.AssetDescID
left join psd_AssetModel o on a.AssetModelID = o.AssetModelID
left join psd_AssetMfr f on a.AssetMfrID = f.AssetMfrID
left join psd_Custodian c on t.CustodianID = c.CustodianID
left join psd_ITDivision i on c.ITDivisionID = i.ITDivisionID
left join psd_Site s on a.SiteID = s.SiteID
where i.ITDivisionID = 10 AND a.IsActive = 1

Thanks again!!!

 

[jby1]

You can use Group By as well, although I don't see why SELECT DISCTINCT will leave any answers out ...

select t.AssetID, a.AssetID, t.CustodianID, a.AssetTag,
a.IsActive, a.AssetDescID, a.AssetMfrID, a.AssetModelID,
a.SiteID, d.AssetDescID, d.AssetDesc, f.AssetMfrID, 
o.AssetModelID, s.SiteID, f.MfrDesc, o.AssetModelDesc, 
s.SiteName, a.IsBroken, c.CustodianID, c.ITDivisionID, 
i.ITDivisionID, i.ITDivShortName 
from psd_Asset a 
left join psd_AssetCustodian t on a.AssetID = t.AssetID 
left join psd_AssetDesc d on a.AssetDescID = d.AssetDescID 
left join psd_AssetModel o on a.AssetModelID = o.AssetModelID 
left join psd_AssetMfr f on a.AssetMfrID = f.AssetMfrID 
left join psd_Custodian c on t.CustodianID = c.CustodianID 
left join psd_ITDivision i on c.ITDivisionID = i.ITDivisionID 
left join psd_Site s on a.SiteID = s.SiteID 
where i.ITDivisionID = 10 AND a.IsActive = 1
group by t.AssetID, a.AssetID, t.CustodianID, a.AssetTag,
a.IsActive, a.AssetDescID, a.AssetMfrID, a.AssetModelID,
a.SiteID, d.AssetDescID, d.AssetDesc, f.AssetMfrID, 
o.AssetModelID, s.SiteID, f.MfrDesc, o.AssetModelDesc, 
s.SiteName, a.IsBroken, c.CustodianID, c.ITDivisionID, 
i.ITDivisionID, i.ITDivShortName

 

[SQLSister]

Show us the data so we can see what is happening.

Questions about posting. See faq183-874

 

[quinnipiac0807]

Alright, I'll post some data but here's my thing. Both the distinct and the group by statements work. Thank you by the way. But, there's an inheritance problem here and I'd rather fix it then work around it. I guess that's my explaination for why I'm doing this. Here is the resulting errant data. I shortened the statement so that you could understand the data. I've narrowed the problem down to this line which is giving me 8 repeated rows:
left join psd_AssetCustodian t on a.AssetID = t.AssetID

Here's the abridged statement:


select a.AssetID, a.AssetTag, a.IsActive, a.AssetDescID, a.AssetMfrID, a.AssetModelID,
a.SiteID, a.IsBroken, c.CustodianID, c.ITDivisionID,
i.ITDivisionID, i.ITDivShortName
from psd_Asset a
left join psd_AssetCustodian t on a.AssetID = t.AssetID
left join psd_Custodian c on t.CustodianID = c.CustodianID
left join psd_ITDivision i on c.ITDivisionID = i.ITDivisionID
where i.ITDivisionID = 10 AND a.IsActive = 1

And here's the returned data, repeated 8 times:

33 00007623 1 18 16 21 9 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
31 00004976 1 19 12 19 10 0 33 10 10 ITD
33 00007623 1 18 16 21 9 0 33 10 10 ITD