Help Converting nested IN statemens to JOINS

[ruckrock]

Hi,

I was wondering what the best query conversion for the following code would be if I had to use JOINS instead of using nested IN statements. I know job_id is more selective than the location_id, so job_id should be in the first where condition I suppose, but my JOIN knowledge isnt what it is supposed to be right now.

select * from user_master where user_id IN
(select user_id from user_role_job where job_id='000000000102' and user_id IN
(select user_id from user_location where location_id IN
(select location_id from gen_location where location_id='000009'))) and discontinued_date

 

[LNBruno]

...and discontinued_date

You left off the rest of the homework assignment...

< M!ke >
I am not a hamster and life is not a wheel.

 

[gmmastros]

To get you started....

select * 
from   user_master 
       Inner Join user_role_job
          On user_master.user_id = user_role_job.User_id
       Inner Join user_location
          On user_master.user_id = user_location.user_id
       Inner Join gen_location
          On user_location.location_id = gen_location.location_id
where  user_role_job.job_id = '000000000102' 
       and gen_location.location_id = '000009'

-George

"the screen with the little boxes in the window." - Moron

 

[ruckrock]

Sorry, here is the full thing

select * from user_master where user_id IN
(select user_id from user_role_job where job_id='000000000102' and user_id IN
(select user_id from user_location where location_id IN
(select location_id from gen_location where location_id='000009'))) and discontinued_date IS NULL

 

[ESquared]

is this a schoolwork assignment?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ruckrock]

No, this is not a homework assignemtnt. I have been giving the task at my job to rework a stored_proc that sends values to Crystal Report in order to optimize its performance. Right now, its generating reports kinda slow for some search criteria.

Basically, I am trying to rework this following block of code into a better query, which I am building from the ground up

The original block of code is:

-- Select Data From A View --

SELECT RTRIM(usr.name_last) + ', '+ RTRIM(usr.name_first) + ' (' + RTRIM(usr.user_login) + ')' AS student_name,
dbo.fnc_GetStudentStatus(usr.discontinued_date, GETDATE()) AS status,
loc.region_name,
loc.location_name,
ttl.job_cat_name,
ttl.description,
rol.role_name

FROM dbo.user_master_view usr
INNER JOIN education_security.dbo.vw_getUsersAccessMaxLevel [max] ON usr.user_id = [max].user_id
INNER JOIN education_security.dbo.role_master rol ON [max].max_level = rol.[level]
LEFT OUTER JOIN @tempJobTitles ttl ON [max].user_id = ttl.user_id AND usr.user_id = ttl.user_id
LEFT OUTER JOIN @tempAvailLocations loc ON usr.user_id = loc.user_id AND usr.home_location = loc.location_id
WHERE
(usr.home_location IN (SELECT location_id FROM @locTable) OR
loc.region IN (SELECT region FROM @busTable)) AND
ttl.code IN (SELECT code FROM @jobTable) AND
dbo.fnc_GetCurrentStatus(@status, usr.discontinued_date, GETDATE()) = @status AND
rol.role_id IN (SELECT role_id FROM @rolTable)


And thus far, the new query I am trying to build( by reverse engineering the previous block of code/eliminating performance hogs, etc) looks like this (thanks to the
gmmastros for the help):

select *
from user_master
Inner Join user_role_job
On user_master.user_id = user_role_job.User_id
Inner Join user_location
On user_master.user_id = user_location.user_id
Inner Join gen_location
On user_location.location_id = gen_location.location_id
where user_role_job.job_id = '000000000102'
and gen_location.location_id = '000009'
and user_master.discontinued_date is null



I am eventually going to have all variables instead of hardcoded column values, but needed to make sure I had all my criteria in first.

 

[ruckrock]

If you want/need to see the whole code for this proc, please see my previous thread

http://www.tek-tips.com/viewthread.cfm?qid=1387353