April 28, 2014 at 4:28 am
I have 6 tables in a query and need to get records from tables like TBL_A,TBL_B and Rest of the 4 tables are common
I need to get data from TBL_B .if records exist in TBL_A
for that p_id (when pref_ty_cd='PREF_BA' then I need to show these rows from TBL_A also. its basically a union
but the problem is i have 4 other tables which are in common.
As of now I have written the query using UNION as mentioned below , but Can I write a query with out union ?
I have given the sample data as below
--drop table TBL_B
--drop table TBL_A
WITH TBL_A (id,p_id,pref_ty_cd,pref_cd,pref_val) AS
(
SELECT 1,100782,'PREF_BA','BA',788 UNION ALL
SELECT 2,100782,'PREF_BA','BA',850 UNION ALL
SELECT 3,100782,'PREF_BA','BA',912
)
SELECT * into TBL_A FROM TBL_A
go
WITH TBL_B (id,p_id,FirstName,lastname,SSN,aff_id,Stat,p_typ) AS
(
select 1,100782,'peter','Agent','ccccc',788,'Active' ,'IP'union all
select 1,100783,'david','Agent','aaaaa',180,'Active' ,'BP'union all
select 2,100784,'victor','Agent','bbbbb',145,'Active','0P'
)
SELECT * into TBL_B FROM TBL_B
go
select p_id,FirstName,lastname,SSN,aff_id,Stat,p_typ from TBL_B
-- left outer join tbl_c --condition
-- left outer join tbl_d --condition
-- left outer join tbl_e --condition
-- left outer join tbl_f --condition
union
select TBL_A.p_id,FirstName,lastname,SSN,pref_val,Stat,p_typ from TBL_B
left outer join TBL_A on TBL_A.p_id=TBL_B.p_id
-- left outer join tbl_c --condition
-- left outer join tbl_d --condition same as above
-- left outer join tbl_e --condition
-- left outer join tbl_f --condition
where pref_ty_cd='PREF_BA'
order by p_id
for p_typ in 'BP','0P' I will get the rows from TBL_B
for p_typ in 'IP' I will get all rows from TBL_a for that p_id
Please suggest me if there is way to avoid repetition of joins by avoiding UNION
and get the correct result
April 28, 2014 at 5:22 am
Try below
SELECT TBL_B.p_id,
FirstName,
lastname,
SSN,
CASE WHEN TBL_A.p_id IS NULL
THEN TBL_B.aff_id
ELSE TBL_A.pref_val
END aff_id ,
Stat,
p_typ
FROM TBL_B
LEFT OUTER JOIN TBL_A on TBL_A.p_id=TBL_B.p_id AND pref_ty_cd='PREF_BA'
-- left outer join tbl_c --condition
-- left outer join tbl_d --condition same as above
-- left outer join tbl_e --condition
-- left outer join tbl_f --condition
order by p_id
April 28, 2014 at 5:23 am
You can use the CASE statement to select the conditional value you need:
/*
for p_typ in 'IP' I will get all rows from TBL_a for that p_id
for p_typ in 'BP','0P' I will get the rows from TBL_B
*/
select
CASE TBL_B.p_typ
WHEN 'IP' THEN TBL_A.p_id
--WHEN 'xx' THEN TBL_C.field
ELSE TBL_B.p_id
END as p_id
,FirstName,lastname
,SSN
,CASE TBL_B.p_typ
WHEN 'IP' THEN pref_val
--WHEN 'xx' THEN TBL_C.field
ELSE aff_id
END as prev_val
,Stat
,p_typ
from TBL_B
left outer join TBL_A on TBL_A.p_id=TBL_B.p_id
-- left outer join tbl_c --condition
-- left outer join tbl_d --condition same as above
-- left outer join tbl_e --condition
-- left outer join tbl_f --condition
where ISNULL(pref_ty_cd, 'PREF_BA') = 'PREF_BA'
order by p_id
April 28, 2014 at 6:59 am
thank you megha and Hanshi for your replies
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply