May 8, 2009 at 3:24 am
I have 3 tables with data as below.
create table user_table (
userid int not null identity(1,1),
username varchar(30)
)
create table subject_table (
subjectid int not null identity(1,1),
userid int,
subject_chosen varchar(30)
)
create table subject_parts_table (
partsid int not null identity(1,1),
userid int,
subjectid int,
subject_parts varchar(30)
)
insert into user_table (username)
select 'myuser1'
union
select 'myuser2'
insert into subject_table (userid,subject_chosen)
select 2,'Physics'
union
select 1,'Chemistry'
union
select 2,'Biology'
insert into subject_parts_table (userid,subjectid,subject_parts)
select 2,2,'Part-1'
union
select 1,1,'Part-1'
union
select 2,2,'Part-2'
union
select 2,null,null
union
select 2,3,'Part-2'
select * from user_table
select * from subject_table
select * from subject_parts_table
The below query gives me the result I expect.
select spt.subject_parts
from user_table ut
inner join subject_table st
on st.userid = ut.userid
right outer join subject_parts_table spt
on spt.subjectid = st.subjectid
where spt.userid = 2
output:
subject_parts
NULL
Part-1
Part-2
Part-2
I have the below query which I CANNOT modify. This is an old query that is dynamically built and being used for years now.
select .....
from user_table ut
inner join subject_table st
on st.userid = ut.userid
where ut.userid = 2
I can only add/join subject_parts_table to this query and get the above subject_parts resultset. Can someone tell me how I can build the query to achieve this?
-- I tried this..
select spt.subject_parts
from user_table ut
inner join subject_table st
on st.userid = ut.userid
right join subject_parts_table spt
on spt.subjectid = st.subjectid
where ut.userid = 2
-- I don't get the NULL row
This gives the below result:
subject_parts
Part-1
Part-2
Part-2
-- I tried the below query. Here, I get the NULL row. But, this query
-- returns all the rows twice. But my problem is I can't use DISTINCT.
select spt.subject_parts
from user_table ut
inner join subject_table st
on st.userid = ut.userid
inner join subject_parts_table spt
on spt.userid = ut.userid
where ut.userid = 2
Please suggest!!
May 8, 2009 at 4:22 am
Since you don't have the religion to modify existing part of the query, you may not have many options to do. Here is one way of doing it...
SELECT spt.subject_parts
FROM user_table ut
INNER JOIN subject_table st ON st.userid = ut.userid
INNER JOIN
(
SELECT ROW_NUMBER() OVER( PARTITION BY userid ORDER BY subjectid ) AS rn, userid, subjectid
FROM subject_table
) stc ON ut.userid = stc.userid AND stc.rn = 1
LEFT JOIN subject_parts_table spt ON spt.userid = ut.userid
AND
(
( spt.subjectid IS NOT NULL AND spt.subjectid = st.subjectid )
OR ( spt.subjectid IS NULL AND st.subjectid = stc.subjectid )
)
WHERE ut.userid = 2
--Ramesh
May 8, 2009 at 6:27 am
Excellent!! Thank you so much Ramesh!! Its working perfectly.
Can you please explain how this part works..
Ramesh (5/8/2009)
INNER JOIN
(
SELECT ROW_NUMBER() OVER( PARTITION BY userid ORDER BY subjectid ) AS rn, userid, subjectid
FROM subject_table
) stc ON ut.userid = stc.userid AND stc.rn = 1
LEFT JOIN subject_parts_table spt ON spt.userid = ut.userid
AND
(
( spt.subjectid IS NOT NULL AND spt.subjectid = st.subjectid )
OR ( spt.subjectid IS NULL AND st.subjectid = stc.subjectid )
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply