Help with query - 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

Transact-SQL (2000)

Help with query

Author

Topic

daisy
Starting Member

13 Posts

Posted - 2005-10-06 : 11:11:43

My SP is this:

@promotion_id int

as

SELECT
coalesce(vm.vs_det_id, vsd.vs_det_id)
, c.client_id
,selected_yn
,CASE WHEN City_Name is null then Client_Name
WHEN RTrim(City_Name) = '' then Client_Name
ELSE City_Name+' ('+Client_Name+')'
END as city_client_name

from
member_order mo
join client c on (c.client_id = mo.client_id)
join sale_breakout sb on (sb.job_id = mo.job_id and
sb.client_id = mo.client_id)
join job_version jv on (jv.version_id = mo.version_id and
jv.job_id = sb.job_id)
join vs_detail vsd on (vsd.version_id = jv.version_id)
join version_split vs on (vs.vs_id = vsd.vs_id and
vs.promotion_id = @promotion_id)
join vs_member vm on (vm.vs_det_id = vsd.vs_det_id and
vm.client_id = mo.client_id )

where
MO.active_yn = '1'

results looking like this:

Id Client_Id Select_Yn Client_City
65 27 1 AKRON
65 65 1 LOS ANGELES
65 66 NULL MIAMI
65 71 NULL NASHVILLE
65 73 NULL NORFOLK
65 75 NULL OMAHA
66 75 NULL OMAHA
65 78 NULL PHILADELPHIA
65 80 NULL PITTSBURGH
65 105 NULL TOLEDO
65 107 NULL TULSA
65 111 NULL WASHINGTON, DC

what I need is:

Id Client_Id Select_Yn Client_City
65 27 1 AKRON
65 65 1 LOS ANGELES
65 66 NULL MIAMI
65 71 NULL NASHVILLE
65 73 NULL NORFOLK
65 75 NULL OMAHA
65 78 NULL PHILADELPHIA
65 80 NULL PITTSBURGH
65 105 NULL TOLEDO
65 107 NULL TULSA
65 111 NULL WASHINGTON, DC

It doesn't matter which Id is being used.
Easy enough right? I've tried using distinct with client_id.
Ideas?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-06 : 11:46:15

Evaluate your join conditions to isolate the root cause of these duplicates. You can probably remove them by altering a join statements.

If it doesnt matter which ID select the MIN(ID) and group by the rest of the columns.

Nathan Skerl

daisy
Starting Member

13 Posts

Posted - 2005-10-06 : 12:04:28

Thank you Nathan...
and I'll just keep on fudging my way through
Sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources