Hi,

Below are my code that I modify to use join
where the @w_c_fab_nm & @i_user_id
are parameters pass in by user.

After my modification, my result is wrong, pls
advise how to enhance my code.

Thank you

**
-- Modify code
DECLARE @w_fab_id char(50)

-- get company id
select @w_fab_id = co_id
FROM Org_co
WHERE co_nm = @c_fab_nm


select a.product_id, a.product_nm, a.version
--product
from pd_product a
inner join
(
select user_id
from user_data_access_grp uda11
inner join
(

select distinct user_grp_id
from pd_security pds11
inner join
(
select product_id
from pd_security ps
inner join (
select a.user_grp_id
from user_data_access_grp a
inner join user_grp b
on a.user_id = @i_user_id
and convert(varchar(10),a.exp_dt,21) = '9999-12-31'
and a.user_grp_id = b.user_grp_id
inner join
user_grp_org c
on b.user_grp_id = c.user_grp_id
and c.co_id = @w_fab_id
) psi
on ps.user_grp_id = psi.user_grp_id

) pds12
on pds11.product_id = pds12.product_id

) uda12
on uda11.user_grp_id = uda12.user_grp_id
) a1
on a.create_user_id = a1.user_id
and a.lock = 0
and a.release = 1
and convert(varchar(10),a.exp_dt,21) = '9999-12-31'
and a.version = (select max(version)
from pd_product
where product_id = a.product_id
and lock = 0
and release = 1
and convert(varchar(10),exp_dt,21) = '9999-12-31')
order by a.product_nm
GO

**

While the code below are my original SQL code

**
-- Original Code

DECLARE @w_fab_id char(50)

-- get company id
select @w_fab_id = co_id
FROM Org_co
WHERE co_nm = @c_fab_nm


select a.product_id, a.product_nm, a.version
--product
from pd_product a
where a.version = (select max(version)
from pd_product
where product_id = a.product_id
and lock = 0
and release = 1
and convert(varchar(10),exp_dt,21) = '9999-12-31'
)
and a.lock = 0
and a.release = 1
and a.create_user_id in
(select user_id
from user_data_access_grp
where user_grp_id in
(
select distinct user_grp_id from pd_security
where product_id
in (select product_id
from pd_security
where user_grp_id in
(
select a.user_grp_id
from user_data_access_grp a, user_grp b, user_grp_org c
where a.user_id = @i_user_id
and convert(varchar(10),a.exp_dt,21) = '9999-12-31'
and a.user_grp_id = b.user_grp_id
and b.user_grp_id = c.user_grp_id
and c.co_id = @w_fab_id

)
)
)
)
and convert(varchar(10),a.exp_dt,21) = '9999-12-31'
order by a.product_nm
GO

**


>You can try JOIN.

SELECT DISTINCT p.pub_name
>FROM publishers p
>JOIN titles t ON p.pub_id = t.pub_id
>

Didi you analyze execution plan of your queries to determine what could slow them down?
>
>>I like to know how do we optimize SQL code
>>using IN into separate query from
>>
>>USE pubs
>>SELECT pub_name
>>FROM publishers
>>WHERE pub_id IN <============
>> (SELECT pub_id
>> FROM titles
>> WHERE type = 'business')
>>
>>Is there any better way to query the subquery first,
>>then place the result inside the IN (...)
>>
>>Like
>>
>>USE pubs
>>SELECT @result = pub_id
>> FROM titles
>> WHERE type = 'business'
>>
>>
>>SELECT pub_name
>>FROM publishers
>>WHERE pub_id IN (@result)
>>
>>
>>Since most of my SQL stored procedure is slowing down
>>due to subquery. I am trying to optimize it
>>and not sure why some query is working, some is halting
>>my DB (lock). It does not have this problem when my
>>DB size is less than 1GB.
>>