July 17, 2007 at 2:39 pm
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
July 17, 2007 at 2:51 pm
July 17, 2007 at 3:39 pm
Just be careful with possible duplicates that can come from "user_role_job"
* Noel
July 18, 2007 at 7:15 am
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
July 18, 2007 at 7:49 am
July 18, 2007 at 1:32 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply