JOINS instead of using IN

Question

Post reply

JOINS instead of using IN

usptart

Grasshopper

Points: 17
More actions
July 17, 2007 at 2:39 pm

#124917

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 IS NULL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

try this

select a.* from user_master a

join user_role_job b on a.user_id = b.user_id

join user_location c on b.user_id = c.user_id

join gen_location d on c.location_id = d.location_id

where b.job_id = '000000000102' and

d.location_id = '000009' and

a.discontinued_date is null

noeld SSC Guru Points: 96590 More actions · Answer 2

Just be careful with possible duplicates that can come from "user_role_job"

* Noel

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

It also depends on what data you are trying to get at...whether or not you'll use a left outer join, a right outer join, an inner join, a cross join, or a full outer join. Also, the first responder assumed that you only wanted data from the first table. You could want data from multiple tables. I hope that this helps. Thanks.

Chris

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

The statement he is currently using only returns data from the first table so I did the same. Noel brought up a good point about duplicates if the fields we are joining on are duplicated.

usptart Grasshopper Points: 17 More actions · Answer 5

Thanks for the help guys.....I ultimately came up producing the following code from your help:

SELECT RTRIM(a.name_last) + ', '+ RTRIM(a.name_first) + ' (' + RTRIM(a.user_login) + ')' AS student_name,

CASE

WHEN a.discontinued_date IS NOT NULL AND CONVERT(datetime, a.discontinued_date, 101) <= CONVERT(datetime,getdate(),101)

THEN 'InActive'

ELSE 'Active'

END AS STATUS,

h.server_name as region_name,

d.short_name as location_name,

e.job_name as job_cat_name,

e.description + ' - ' + e.ext_ref_code_1 as description,

g.role_name as role_name

FROM user_master a

JOIN user_role_job b on a.user_id = b.user_id

JOIN user_location c on b.user_id = c.user_id

JOIN gen_location d on c.location_id = d.location_id

JOIN job_master e on b.job_id = e.job_id

JOIN user_role_app f on a.user_id = f.user_id

JOIN role_master g on f.role_id = g.role_id

JOIN gen_server h on d.server_id = h.server_id

--JOIN education_security.dbo.vw_getUsersAccessMaxLevel vwML ON a.user_id = vwML.user_id

--INNER JOIN education_security.dbo.role_master rm ON vwML.max_level = rm.level

WHERE b.job_id = '000000000102' and

d.location_id = '000009' and

a.discontinued_date is null

I still got some work to do with the code that comes before this query....but..this helped alot.