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.

 

[SQLBill]

How about:

SELECT a.Field1, a.Field2
 FROM TableA a
  INNER JOIN TableB b
   ON a.Field1 = b.Field1
    AND a.Field2 <> b.Field2

-SQLBill

Posting advice: FAQ481-4875

 

[pradchal1]

I have tried this before. This gives a bunch of duplicate rows.

The output table would look like

Field 1 Field 2
5 D
5 E
5 D
5 E
6 C
6 D
6 C
6 D

 

[amrita418]

Add distinct keyword the select stmt.

 

[pradchal1]

No that still won't work because if we have tables like this

Table A

5 A
5 B

And Table B

5 A
5 B

The query should not be getting any output since the records match on both the tables ( I am looking for records that match on first field but not on the second field).
But with your solution, on my output table I would have thses output:

Output table

5 A
5 B

 

[vongrunt]

Looks like classic left join to me...

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

------
"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)

 

[vongrunt]

(* after reading post once more *)

Scrap that.

Yes, DISTINCT on query posted by SQLBill should do the trick.

------
"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)

 

[amrita418]

I am not sure what you are talking about but the query works on your sample input.

create table #TableA (Field1 int, Field2 char(1))

insert into #TableA values (3,           'A')
insert into #TableA values (5,           'D')
insert into #TableA values (5,           'E')
insert into #TableA values (6,           'A')
insert into #TableA values (6,           'B')


create table #TableB (Field1 int, Field2 char(1))

insert into #TableB values (3,           'A')
insert into #TableB values (5,           'A')
insert into #TableB values (5,           'B')
insert into #TableB values (6,           'C')
insert into #TableB values (6 ,          'D')

select     distinct 
           a.field1, 
           a.field2
from       #TableA a 
           inner join #tableB b 
           on (a.field1 = b.field1 
               and a.field2 != b.field2)

 

[gmmastros]

Amrita,

I think the problem with your solution is that it would bring back the wrong results. For example, if you add (to table a)

insert into #TableA values (5, 'A')

Then, 5-A exists in both tables and should not be returned. Using your code, the value is returned.

In my opinion, vongrunt was correct (before he recanted) regarding the classic left join.

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

-George

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

 

[vongrunt]

I'm not sure... original requirement was "...where field 1 matches but field 2 doesn't.". Left join would also return rows that don't have any field matched.

------
"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)

 

[amrita418]

How about this?

select     distinct 
           a.field1, 
           a.field2
from       #TableA a 
           inner join #tableB b 
           on (a.field1 = b.field1 
               and a.field2 != b.field2)
where     not exists (select 'x' 
                      from   #Tableb c
                      where  a.field1 = c.field1 
                             and a.field2 = c.field2)

Regards,
AA

 

[pradchal1]

This is getting quite confusing. But simply having INNER JOIN and putting DISTINCT does not work. Like gmmastros and I mentioned before, it would bring back the record where both the fields match. We just one records were field1 matches and field 2 doesn't, in general.

 

[vongrunt]

ARGH.... (no I'm not yelling at you)...

This is not "simply INNER JOIN" - notice <> (!=) operator.

------
"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]

VONGRUNT,
If you have tables like the following:

Table A

4 A
5 A
5 B

Table B

4 B
5 A
5 B


Will your suggestion give me an output table like this:

4 A



The main problem is, I have two supposed to be exactly similar tables, but some how the second field seems to have differed. So what I am trying to do is, from Table A, I am trying to get all the records that have different second field in table B. And using the inner join and Distinct, I would also get
5 A
5 B.

 

[gmmastros]

Yes, use the Left Outer Join method, like vongrunt originally suggested.

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


-George

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

 

[amrita418]

Did you try the code that I posted with the not exists clause? Do you get an unwanted data using that?

 

[pradchal1]

Amrita, I tried your code, but it did not work either. I have decided to just use the LEFT OUTER JOIN suggested by Vongrunt and that has worked.

The reason why your code did not work was
Table A Table B
A Null A 1
A Null A 2
A 3 A 3
B 4 B 4
B 5 B 5

I still got

A Null
A 3
B 4
B 5

instead of
A Null
A Null

 

[vongrunt]

Hm... this is definitely confusing .

From last sample, remove rows (B, 4) and (B, 5) from Table B. LEFT JOIN still returns these two rows, right?

I'd say amrita made it right... but I also brainfarted twice in this thread (hard day, don't ask) so... shall we make better data sample and start all over again?

------
"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]

The easiest logic I can think of and have tried using MS ACCESS is by creating a 'KEY' field which is a combination of field1 and field 2, on both the tables. So now, all I have to do is, table A LEFT OUTER JOIN table B on A.key = B.key where B.Key is NULL.

But I was trying to use the same logic in SQL statement.

 

[vongrunt]

Let's try to make things a little bit more clear... original requirement was:

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

In other words: show rows from tableA with at least one partial match (Field1) but no full matches (Field1, Field2).

Sample data:

create table TableA (Field1 int, Field2 char(1))
create table TableB (Field1 int, Field2 char(1))

-- one full match
insert into TableA values (1, 'A')
insert into TableB values (1, 'A')
-- one partial match
insert into TableA values (2, 'A')
insert into TableB values (2, 'B')
-- one partial, one full match
insert into TableA values (3, 'A')
insert into TableB values (3, 'A')
insert into TableB values (3, 'B')
-- two partial matches
insert into TableA values (4, 'A')
insert into TableA values (4, 'B')
insert into TableB values (4, 'C')
insert into TableB values (4, 'D')
-- no match at all
insert into TableA values (5, 'A')

Expected results:

Field1 Field2
2	    A
4	    A
4	    B

Is that correct?


------
"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)