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