March 26, 2013 at 5:41 pm
Hi,
I have 2 tables
Table Member with columns
A B C D
apple bread carrot Chocolate
Mango Begal Onion Candy
Table Util Columns
A B C D X y
apple bread carrot Chocolate coffee chedder
Mango Begal Onion Candy tea Provolon
Kiwi biscuit garlic peanut greentea mozrella
Now I want to know weather table UTIL have any data not in table MEMBER ONLY on columns A,B,C,D
I wrote following query ---dd not give me result
Select * from Util U
Left Outer join on Member m
on U.A=m.A
AND U.B=m.B
AND U.C=m.C
AND U.D=m.D
where
m.A is NULL OR
m.B is NULL OR
m.C is NULL OR
m.D is NULL
Next query I wrote was like this--and this gives me result the last row in the table Util.
Select * from Util U
where
U.A not in (Select A from Member)
or U.B not in (Select B from Member)
or U.C not in (Select C from Member)
OR U.D not n (Select D from Member)
I want to know what is wrong in my 1st query, why left outer join is not working.
.
Kindly please help.
Thanks
March 27, 2013 at 12:17 am
It looks like you have a syntax error with the query that you posted with on before the member table
Select * from Util U
Left Outer join ON Member m
on U.A=m.A
AND U.B=m.B
AND U.C=m.C
AND U.D=m.D
where
m.A is NULL OR
m.B is NULL OR
m.C is NULL OR
m.D is NULL
I tried it without the ON and it seems to work OK
; with member as (
select *
from (
select 'apple', 'bread', 'carrot', 'Chocolate'
union all
select 'Mango', 'Begal', 'Onion', 'Candy'
) as m(a, b, c, d)
)
,util as (
select *
from (
select 'apple', 'bread', 'carrot', 'Chocolate', 'coffee', 'chedder'
union all
select 'Mango', 'Begal', 'Onion', 'Candy', 'tea', 'Provolon'
union all
select 'Kiwi', 'biscuit', 'garlic', 'peanut', 'greentea', 'mozrella'
) as u(a,b,c,d,x,y)
)
Select *
from Util U
Left Outer join Member m on
U.A=m.A
AND U.B=m.B
AND U.C=m.C
AND U.D=m.D
where
m.A is NULL OR
m.B is NULL OR
m.C is NULL OR
m.D is NULL
March 27, 2013 at 7:01 am
Nulls will be the problem because you use = operator. Use except operator, it gives correct result and is much simpler:
Select a, b, c, d from util
Except
Select a, b, c, d from firsttable
March 27, 2013 at 7:05 am
Well ON was by mistake I entered, my actual query doesn't have ON after left outer join, Only Left outer join table name .
Here what Business rule says,
A Utili record does not have a matching Member record where the amount > 0. based on Column A,B,C,D
And left outer join is not capturing error.
Thanks For the reply.
March 27, 2013 at 7:14 am
Well Except is a good idea, but if the error exit I will have to Insert the error in Error table.
Can we use "except" to handle this situation ?
SO it like this
Insert Into Error table
ID.
Row_NUM,
ERR_CD,
ERR_MSG
select
Util_ID as ID,
Util_Row as ROW_NUM,
'Error123' as ERR_CD,
AMT AS ERR_MSG
From
Util U
Left Outer join Member m
on U.A=m.A
AND U.B=m.B
AND U.C=m.C
AND U.D=m.D
where
m.A is NULL OR
m.B is NULL OR
m.C is NULL OR
m.D is NULL
Thanks a lot
March 27, 2013 at 7:25 am
insert into error(a, b, c, d, e, f, reason)
select u.a, u.b, u.c, u.d, u.e, u.f, reason='not in member'
FROM util u
join
(
Select a, b, c, d from util
Except
Select a, b, c, d from member
) t ON (u.a = t.a or u.a is null and t.a is null)
AND (u.b = t.b or u.b is null and t.b is null)
AND (u.c = t.c or u.c is null and t.c is null)
AND (u.d = t.d or u.d is null and t.d is null)
March 28, 2013 at 9:15 am
create table #member (A char(10), B char(10), C char(10), D char(10))
insert #member (A, B, C, D)
values ('apple', 'bread', 'carrot', 'chocolate'),
('Mango', 'Begal', 'Onion', 'Candy')
create table #Util (A char(10), B char(10), C char(10), D char(10), X char(10), y char(10))
insert #Util (A, B, C, D, X, y)
values ('apple', 'bread', 'carrot', 'Chocolate', 'coffee', 'cheddar'),
('Mango', 'Bagel', 'Onion', 'Candy', 'tea', 'Provolone'),
('Kiwi', 'biscuit', 'garlic', 'peanut', 'green tea', 'mozzarella')
select y.food from #Util unpivot(food for xx in (A, B, C, D, X, y)) as y
except
select x.food
from #member
unpivot(food for xx in (A, B, C, D)) as x
drop table #member
drop table #Util
March 28, 2013 at 9:26 am
Thanks everyone.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy