Problem Getting Distinct Records

I have 2 tables that I'm trying to join. The first table is called DeviationMaster and the second is called DeviationDist
I have no problem returning all results for deviationMaster. My problem is, when I try and join DeviationDist to DeviationMaster I get alot of duplicate records. DeviationDist contains a column named DEVDN which is the Deviation Number column. It also contains a column DEVDD which is the Distributor name. I join them by saying where DNumber (which is the Deviation column in DeviationMaster) is equal to DEVDN in DeviationDist. This works fine when I only select the DEVDN column, but I really need the DEVDD column as well. When I grab the DEVDD column, it then shows duplicate records since there can be multiple DEVDD for each DEVDN in DeviationDist.
I guess I need to know how I can only grab 1 occurance for each DEVDN in DeviationDist and relate that to the DeviationMaster table.
This statement works but does not get DEVDD which is the dist number column that I need to get. This of course doesnt duplicate.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
This statement gets the DEVDD column but now I may have multiple Deviations present depending on how many times DEVDD repeats for each DEVDN. I need to grab only 1 of each DEVDD and DEVDN.
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC

I'm stuck, any help is appreciated.

Hello!
Is guess this is a small controversy within SQL - but you should review the DISTINCT versus the GROUP BY - there is a lot of different opinoins there but from my humble position within this coding world
I always use the Group By and I always get what I want....
SELECT distinct dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC|||Sorry --
Please remove Select Distinct and Replace with just a Select
Best Regards,
Joe|||

This could be a solution, i think:
SELECT max(dm.DNumber), max(dm.TNumber), max(dm.Status), max(dm.DType), dd.DEVDD, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
ORDER BY dm.DNumber DESC
GROUP by dd.DEVDD, dd.DEVDN
but this would make DeviationDist the Mastertable and DeviationMaster the DetailsTable since it would only by used for aggregates.
This way you would get a summary of DEVDD's ans DEVDN's with (in this case) the maxima of DNumber, TNumber, ...
Hope this helps

|||

I tried

SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
GROUP BY DD.DEVDN
ORDER BY dm.DNumber DESC
I get an error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.TNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dm.DType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||This still produced multiple records.|||You could do something like this :
SELECT dm.DNumber, dm.TNumber, dm.Status, dm.DType, dd.DEVDN
FROM DeviationMaster As dm, DeviationDist As dd
WHERE dm.DNumber = dd.DEVDN
AND dd.DEVDN IN ( SELECT dd.DEVDN FROM DeviationMaster dm INNER JOIN DeviationDist dd ON dm.DNumber = dd.DEVDN GROUP BY DD.DEVDN)
ORDER BY dm.DNumber DESC
|||

I still need to grab column dd.DEVDD...

Also, these are still returning 35xxxx records and the DeviationMaster only has 8xxx records. I didnt know it wa sso complicated to only grab the first dist is sees for each DEVDN and DNumber...

|||

Lets say for DeviationDist there is DEVDD and DEVDN

If I say select distinct DEVDN, it returns 8xxx records which is good. I need it to do the same when DEVDD is added to the mix but it goes right to 35xxx records if I do distinct devdd, devdn...
Isnt there a way to say for each devdn, get the devdd but ignore the rest?

|||can you provide some sample data and the kind of resultset that you are expecting back. Also is DevDN the primarykey in the DevDD table ?|||

DeviationDist table has a DEVID Column which is just numeric identity, pk... DEVDN which is Deviation Number and DEVDD which is distributor.

I need only one occurance of the DEVDD per each DEVDN and then I need to join that to the DeviationMaster table on column DNumber which is the deviation number.

Lets say this is the Table:

DEVID DEVDN DEVDD
1 100000 0920292
2 100000 2920292
3 100000 2928373
4 100001 9383939
5 100001 4484949
6 100002 5959595
7 100003 3939393

When I run the query, I only want to see records 1, 4, 6, 7. Since 2, 3 are just 2 more distributors for the same deviation (DEVDN) and same as 5. That way, when I link to the DeviationMaster, I will get a one to one result. When I do Distinct like I said above, it still returns all these but I only want 1 record for each DEVDN.

|||

check this :
SELECT
dd.devdn, min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
Devdn DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY
dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

That returned no results:

SELECT dd.devdn, min(dd.devdd),dm.dnumber, dm.tnumber, dm.status, dm.dtype
FROM DeviationDist DD
INNER JOIN DeviationMaster DM ON dm.dnumber = dd.DevId
GROUP BY dd.devdn, dm.dnumber, dm.tnumber, dm.status, dm.dtype

|||

here's what I tried :

declare @.dm table ( dnumber int, tnumber int, status varchar(10), dtype varchar(10) )
declare @.dd table (pk int, devdn int, devdd varchar(10) )

insert into @.dm values (1,133,'a', 'a')
insert into @.dm values (2,243,'a', 'a')
insert into @.dm values (3,353,'a', 'a')
insert into @.dm values (4,483,'a', 'a')
insert into @.dm values (5,563,'a', 'a')
insert into @.dm values (6,663,'a', 'a')
insert into @.dm values (7,763,'a', 'a')

insert into @.dd values ( 1 , 100000,1920292)
insert into @.dd values ( 2,100000,2920292)
insert into @.dd values ( 3,100000, 2928373)
insert into @.dd values ( 4,100001, 9383939)
insert into @.dd values ( 5, 100001, 4484949)
insert into @.dd values ( 6,100002, 5959595)
insert into @.dd values ( 7, 100003, 3939393)

Select min(ddd.pk),devdn from @.dd ddd group by ddd.devdn

--the above will give you distinct combination of devdn and devdd
--use the above to join to deviation master
select
min(dd.devdn), min(dd.devdd)
,dm.dnumber, dm.tnumber, dm.status, dm.dtype
from
@.dd dd
inner join @.dm dm on dm.dnumber = dd.pk
and
dd.pk in ( Select min(ddd.pk) from @.dd ddd group by ddd.devdn)
group by dd.devdn,
dm.dnumber, dm.tnumber, dm.status, dm.dtype

Here's the resultset:
100000 1920292 1 133 a a
100001 9383939 4 483 a a
100002 5959595 6 663 a a
100003 3939393 7 763 a a

|||I'm not sure why yours works and mine did nothing. Does it matter that DeviationMaster has no PK and that DEVDN is varchar, not integer?
DNumber should be numeric, TNumber is varchar...
I need exactly the result you got, ahh!