query filter advice (calling Dr. Cross Join!)

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

SQL Server Development (2000)

query filter advice (calling Dr. Cross Join!)

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-09-28 : 07:43:23

Good morning kiddies,
have a look at the following code:

select th.tkt_no,rl.item_no,rl.qty_recvd,rl.recvd_cost,rl.po_no,rl.commnt_1
from po_recvr_hist_lin rl
join ps_tkt_hist th
on rl.commnt_1=th.orig_ord_no
right outer join ps_tkt_hist_lin tl
on th.tkt_no=tl.tkt_no
and th.str_id=tl.str_id
and th.sta_id=tl.sta_id
where (th.is_svc_call='Y' or th.is_svc_call='c')
and rl.qty_recvd>=1
and rl.item_no not in (select item_no from ps_tkt_hist_lin)
group by th.tkt_no,rl.item_no,rl.qty_recvd,rl.recvd_cost,rl.po_no,rl.commnt_1

This query is returning the data that we want in our test db, but I am concerned about the subquery. It seems to me that the subquery is not specific enough. Does it need to contain the same join for the tables as in the main query, or am I over-reading this? (Not like that happens more than once a day!)
Or, is there a more efficient way to write this

we are looking for all items in table rl that were recieved for an order (table th orig_ord_no) but not sold on that order (rl.item_no does not exist in tl.item_no)

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources