Nested Query Help - 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)

Nested Query Help

Author

Topic

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-07-16 : 18:45:56

Does anyone know if there is a better way to write the following nested query. I'm not sure if a join would work better here or not?

Any help would be greatly appreciated!

DELETE
FROM veh_inv_part
WHERE part_list_id IN(
SELECT part_list_id
FROM veh_inv_part (NOLOCK)
WHERE inventory_id = @inventory_id
AND part_list_id NOT IN
(SELECT part_list_id
FROM @VEH_OPTIONS
WHERE part_list_id IS NOT NULL
AND AMS_Flg = 'Y'))
AND inventory_id = @inventory_id
AND part_list_id NOT IN (
SELECT part_list_id
FROM veh_inv_part (NOLOCK)
WHERE inventory_id = @inventory_id
AND part_list_id NOT IN
(SELECT part_list_id
FROM av_cust_code_assoc (NOLOCK)
WHERE ams_flg = 'Y'
AND part_list_id is not null))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 02:05:34

isnt the above same as :-

DELETE vip
FROM veh_inv_part vip
LEFT JOIN @VEH_OPTIONS vo
ON vo.part_list_id=vip.part_list_id
AND vo.AMS_Flg = 'Y'
INNER JOIN av_cust_code_assoc vcca
ON vcca.part_list_id=vip.part_list_id
AND vcca.AMS_Flg = 'Y'
WHERE vo.part_list_id IS NULL
AND vip.inventory_id = @inventory_id

dwalker79
Yak Posting Veteran

54 Posts

Posted - 2008-07-17 : 07:37:47

What about the part of my query where I say "part_list_id NOT IN". I don't see how you accounted for that? Can you explain?

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources