April 1, 2008 at 10:21 am
I got 2 tables where there are matching records.
But i need to pull out data from table 2 that is not present in table 1 along with the matching data.
My procedure has 2 joins linked by UNION. It gives the correct result that i need.
But i am trying to avoid UNION. Is there any other way to alter joins and bring out the result?
Sample of my Procedure:
-----------------------
Select * from Table1 A
Join Table2 B
On
Jion Table3 C
On
UNION
Select * from Table2 B
Join Table3 C
on
April 1, 2008 at 10:30 am
Sql Student (4/1/2008)
I got 2 tables where there are matching records.But i need to pull out data from table 2 that is not present in table 1 along with the matching data.
My procedure has 2 joins linked by UNION. It gives the correct result that i need.
But i am trying to avoid UNION. Is there any other way to alter joins and bring out the result?
Sample of my Procedure:
-----------------------
Select * from Table1 A
Join Table2 B
On
Jion Table3 C
On
UNION
Select * from Table2 B
Join Table3 C
on
I'm confused. You want data from 2 tables, but you show 3 tables in the skeleton code above. If you could provide the DDl of the tables and your current code, I'm sure we could determine if there is a way to do what you want with out a join (if possible).
April 1, 2008 at 10:39 am
The 3rd table is just to match certain records so that the result set is a valid one. it doesnt really matter. What matters is the record in table 2 that is not present in table 1 has to be pulled out 🙁
I tried Full outer joins but seems like it throws a huge bunch of unwanted records. My procedure is pretty huge and i doubht if i paste it here, it would be very confusing.
Let me try to paste one after i remove lines. Just try to show only the joins
April 1, 2008 at 10:43 am
I tried Full outer joins but seems like it throws a huge bunch of unwanted records
It sounds like you need a left join. A left join will return all records in the left table, regardless of a match in the right table.
select *
from table2 t2
left join table1 t1
on t2.id = t1.id
April 1, 2008 at 10:48 am
SELECT distinct
--
FROM
PlanBudgetDetail P
join
Allotment A
On
P.id = A.id --match id's
JOIN
Left Join
Tracker_Comments T
on
--
P.fy_dt = T.fy_dt --match the year
and P.appr_cd = T.apprcd --match the codes
and A.id = T.id -- match the id's
WHERE
Basically this above query returns all records present in PlanBudgetDetail but not the ones in Tracker_comments.
The reason is some id's in Tracker_comments are not present in PlanBudgetDetail. PlanBudgetDetail is basically a transaction table.
What i did to pull out those missing records? I did 2 seperate Joins and added a UNION in between
SELECT distinct
--
FROM
PlanBudgetDetail P
join
Allotment A
On
P.id = A.id --match id's
JOIN
Left Join
Tracker_Comments T
on
--
P.fy_dt = T.fy_dt --match the year
and P.appr_cd = T.apprcd --match the codes
and A.id = T.id -- match the id's
WHERE
-- UNION both the join results
UNION
SELECT distinct
--
FROM
Tracker_Comments T
join
Allotment A
On
A.id = T.id --match id's
WHERE
April 1, 2008 at 10:50 am
The UNION kind of hinders performance of the procedure. So i am figuring out how it can be done?
April 1, 2008 at 11:11 am
If you could provide the DDL for the tables (at least the key fields plus any non key fields used in the joins), some sample data (based on the DDL you provide), your current procedure (minus any irrelevant data columns), and what output is expected from the completed query would be beneficial. The code fragments you have provided so far don't help me very much at all.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply