both ways outter join ?

[drewex]

Hi all,

I want to do a outter join in 2 talbes. I need the extra items in both tables. i know i can do it one way doing left or right join but the should be a way to do this.
Generally the script should combine of these to queries but should be in one query so i wont lose to much time

select * from Ta left join Tb on Tb.ID = Ta.ID where Tb.id is Null
select * from Ta right join Tb on Tb.ID = Ta.ID where Ta.id is Null

is there a way to combine these to with out doing
select * from
(select * from Ta left join Tb on Tb.ID = Ta.ID where Tb.id is Null),
(select * from Ta right join Tb on Tb.ID = Ta.ID where Ta.id is Null)
....
or something like that.

[CardboardHammer]

FULL OUTER JOIN

[drewex]

awsome
thanks card..

[CardboardHammer]

You're welcome :cool:

[drewex]

Hey Card it didnt work

i tried;
select * from Ta Full outer join Tb on Tb.ID = Ta.ID
Full outer join TC on Tc.ID2 = Tb.ID2

three tables has extra data but its showin like a left.
I guess, having
where (Ta.t <> Tb.t or Ta.t <> Tc.t or tb.t <> tc.t)

eliminates the Null values. ouch is there way to make it show the null values, too

[drewex]

Ok i caught something if i do the where clause in the join selection it works like :

select * from Ta Full outer join Tb on Tb.ID = Ta.ID
and Ta.t <> Tb.t

ok here is the problem how to extend this to 3 table join
hard one im going to try some combinations. If i can find the solution let you know.

[drewex]

ok ,because it goes by order doing the joins



select * from Ta
Full outer join Tb
on Tb.ID = Ta.ID and Ta.t <> Tb.t

Full outer join TC on Tc.ID2 = Tb.ID2
and (Ta.t <> Tb.t or Ta.t <> Tc.t or tb.t <> tc.t)


looks like working. Am I missing anything what do you think?

[drewex]

Nope didnt work ouch its giving all the possibilities 3 times, im getting 1000's of rows instead of 10's.

[CardboardHammer]

If you move WHERE conditions to the ON clause in a FULL OUTER JOIN, it can really screw with your results, as the WHERE clause will filter out records, but the ON clause will just cause matches to be made or not, but you ALWAYS get every record from each table (unless filtered out in the WHERE clause) at least once, even if you just get all NULLs on the opposite side.

I'm not entirely sure the set of records you're trying to pull out of those 3 tables... Perhaps if you can describe in English what you're trying to pull, I can cook up the SQL.

[drewex]

ok here is what im trying to do, I need to get data that t fields doesnt match and the one's that are not in each other's table. See all the table has to have the same ID rows in them. BUt some times one of the table doesnt get it(data entry error). I need to filter out the ones doesnt fully identical (with the T amount too )and show the ones that are not not on one side or different T amount.
Those this make sense. I can get you a sample table list if you want.

Thanks for the help.

[CardboardHammer]

OK. Let me rephrase it to see if I understand correctly:

Over the 3 tables, show only rows where: the ID isn't found in all 3 tables (just in 1 or 2 of the tables, any combination) OR t doesn't match for those rows where all 3 IDs do match up?

[CardboardHammer]

SELECT * FROM
(TA FULL OUTER JOIN TB
ON TA.ID = TB.ID)
FULL OUTER JOIN TC
ON TC.ID = TA.ID OR TC.ID = TB.ID
WHERE TA.ID IS NULL OR TB.ID IS NULL OR TC.ID IS NULL OR
NOT (TA.t = TB.t AND TA.t = TC.t) --May require more conditions if t can be NULL in any of the 3 tables

[drewex]

Sorry for my late reply i thought u didnt reply i didnt get an auto email from the server, dont no why.

I had the same code except the where clause for is null's at the id's that solved it. I dont know why i couldnt think of that i use it alot. I guess i needed a third eye perspective. Thanks for the help. It looks like it worked. Ill be testing the code for a while I'll let you know if there is any other problems left in it.

Thanks alot

[CardboardHammer]

No sweat. I figured the lack of response was weekend related, and had completely forgotten about it until now.

You're welcome :cool: