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
 General SQL Server Forums
 New to SQL Server Programming
 Select Distinct Column While Joining Tables

Author  Topic 

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-29 : 21:32:08
Hello World,

I am attempting to run the following select statement joining multiple tables but in the end result I would like only Distinct/Unique values to be returned in the invlod.lodnum column. Can this be done?

[select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id
from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
join invlod
on (locmst.stoloc = invlod.stoloc)
and (locmst.wh_id = invlod.wh_id)
join invsub
on (invlod.lodnum = invsub.lodnum)
join invdtl
on (invsub.subnum = invdtl.subnum)
join shipment_line
on (shipment_line.ship_line_id = invdtl.ship_line_id)
join shipment
on (shipment_line.ship_id = shipment.ship_id)
join adrmst
on ('A0000'+shipment.host_ext_id = adrmst.host_ext_id)
left outer join stop
on (stop.stop_id= shipment.stop_id)
left outer join car_move
on (stop.car_move_id = car_move.car_move_id)
left join pckwrk pw on invdtl.wrkref = pw.wrkref
where car_move.car_move_id = 'abcxyz'];

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-30 : 09:10:48
add the word DISTINCT after the opening word "select"
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-10-30 : 09:21:09
or maybe something like:

WITH cte
AS
(
select pw.schbat, adrmst.adrnam, adrmst.adrln1, adrmst.adrcty, adrmst.adrstc, adrmst.adrpsz,
invlod.lodnum,
shipment.host_ext_id, shipment_line.ordnum, car_move.car_move_id

,ROW_NUMBER() OVER (PARTITION BY invlod.lodnum ORDER BY <whatever columns you want>) AS rn

from aremst join locmst
on (aremst.arecod = locmst.arecod)
and (aremst.wh_id = locmst.wh_id)
join invlod
on (locmst.stoloc = invlod.stoloc)
and (locmst.wh_id = invlod.wh_id)
join invsub
on (invlod.lodnum = invsub.lodnum)
join invdtl
on (invsub.subnum = invdtl.subnum)
join shipment_line
on (shipment_line.ship_line_id = invdtl.ship_line_id)
join shipment
on (shipment_line.ship_id = shipment.ship_id)
join adrmst
on ('A0000'+shipment.host_ext_id = adrmst.host_ext_id)
left outer join stop
on (stop.stop_id= shipment.stop_id)
left outer join car_move
on (stop.car_move_id = car_move.car_move_id)
left join pckwrk pw on invdtl.wrkref = pw.wrkref
where car_move.car_move_id = 'abcxyz'
)
SELECT *
FROM cte
WHERE rn = 1;

Go to Top of Page

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-30 : 17:36:42
IFOR, I think you may have it there. I ran the code and for what I see, I beleive that is exactly as I wanted. I messed around a bit with the adding of distinct after the select statement which was I think gbritton you had mentioned but that wasn't working so well. THANK YOU !
Go to Top of Page

Jlamb62480
Starting Member

10 Posts

Posted - 2014-10-30 : 19:28:16
So this code now works perfectly! Once again thanks...
Would there be anything I could do to perform a "count distinct" function on the column "lodnum" where it would simply return the number of distinct values in that column?
Go to Top of Page
   

- Advertisement -