Unmatched Query Help

[pradchal1]

I have two tables:

Table A

Field 1 Field 2
3 A
5 D
5 E
6 A
6 B

Table B

Field 1 Field 2
3 A
5 A
5 B
6 C
6 D

Now I want an output table that has fields from Table A where field 1 matches but field 2 doesn't.

Output Table

Field 1 Field 2
5 D
5 E
6 A
6 B

Please Help.

 

[pradchal1]

That's right Vongrunt.

 

[gmmastros]

OK, I see vongrunt's concern. Using the Left Outer Join, the last record (where the 5 record exists in table a but not b) would be returned by the Left Outer Join.

Would this query work for you?

select A.*
from #TableA A
left outer join #TableB B on A.Field1=B.Field1 and A.Field2=B.Field2
where B.Field1 is null
		And A.Field1 In (Select #TableB.Field1 From #TableB)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Then amrita patched it right - code below is same as his, only names are changed to fit last sample data:

select distinct A.field1, A.field2
from tableA A 
inner join tableB B on A.field1=B.field1 and A.field2 <> B.field2
left outer join tableB C on A.field1=B.field1 and A.field2=B.field2
where not exists 
	(	select * 
		from tableB C 
		where C.Field1=A.Field1 and C.Field2=A.Field2
	)

Same thing without DISTINCT, kinda more human-readable:

select A.*
from TableA A
where A.Field1 = ANY(select Field1 from TableB B where B.Field2<>A.Field2)
	and A.Field1 <> ALL(select Field1 from TableB B where B.Field2=A.Field2)

= ANY() is same as IN(), <> ALL() as NOT IN ().

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pradchal1]

Thanks all you guys for your help and time. I have learnt a lot here today.
Thanks again.

 

[vongrunt]

To George: yes, that should work too.

Btw. this one extra SELECT still itches me a lot so:

select A.field1, A.field2
from TableA A
inner join TableB B on A.Field1=B.Field1
group by A.field1, A.field2
having count(*) = count(nullif(A.field2, B.field2))

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)