Help with a join query

[bigfoot]

I'm tearing my hair out over a couple of simple queries.

I have 2 tables that I need to work with.
One is a production table and the other is an import table.

The stored procedure needs to join the 2 tables on the first 3 fields and if it finds a match then update the production table with the data from the import table.

If the first 3 fields do not match then insert the record from the import table into the production table.

This was the easy part.

If only one or two of the fields match, then the import record is an exception and needs to be inserted into a third table.

The matching fields are item_no, upc, and source. These are all varchar fields and are named the same in both tables.

I created 2 test tables and some test queries to show how I think it should work.

TableA

C1      C2      C3
-------------------
1	A	A1
2	B	A2
3	C	A3
4	D	A4
5	E	A5
6	F	A6

TableB

C1      C2      C3
-------------------
1	A	A1
2	Z	A2
3	D	ZZ
8	E	A5
9	Z	6
4	D	ZZ

-- ONLY RETURN NEW RECORDS (NO MATCHES)
SELECT b.c1,b.c2,b.c3, a.c1,a.c2,a.c3
FROM TableB b
left outer join TableA a
on
b.c1 = a.c1
and
b.c2 = a.c2
and
b.c3 = a.c3
where 
a.c1 is not null
and
a.c2 is not null
and
a.c3 is not null

-- ONLY RETURN NON NEW RECORDS
SELECT b.c1[b.c1],b.c2[b.c2],b.c3[b.c3],a.c1[a.c1],a.c2[a.c2],a.c3[a.c3]
FROM TableB b
left outer join TableA a
on
b.c1 = a.c1
and
b.c2 = a.c2
and
b.c3 = a.c3
where 
a.c1 is null
and
a.c2 is null
and
a.c3 is null

I can't figure out how to get what's in between.
I need the partial matches and I keep getting the others mixed in.

I'm looking for ALL, NOTHING, and INBETWEEN.

Any help would be really great. I've search and worked on this for 2 days.

Thanks so much.

 

[markros]

To return new records only

SELECT b.c1,b.c2,b.c3, a.c1,a.c2,a.c3
FROM TableB b
left outer join TableA a
on
b.c1 = a.c1
and
b.c2 = a.c2
and
b.c3 = a.c3
where
a.c1 [b]is null[/b]

 

[bborissov]

-- ONLY RETURN NON NEW RECORDS
SELECT b.c1 [b.c1],
       b.c2 [b.c2],
       b.c3 [b.c3],
       a.c1 [a.c1],
       a.c2 [a.c2],
       a.c3 [a.c3]
FROM TableB b
INNER JOIN TableA a 
      on b.c1 = a.c1 and
         b.c2 = a.c2
WHERE b.c3 NOT IN (SELECT C3 FROM TableA
                          WHERE TableA.C1 = b.C1 AND
                                TableA.C2 = b.C2)

NOT TESTED AT ALL!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

And to return ONLY OLD RECORDS:

-- ONLY RETURN NON NEW RECORDS
SELECT b.c1[b.c1],b.c2[b.c2],b.c3[b.c3],a.c1[a.c1],a.c2[a.c2],a.c3[a.c3]
FROM TableB b
INNER join TableA a on
      b.c1 = a.c1 and
      b.c2 = a.c2 and
      b.c3 = a.c3

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bigfoot]

Thank you both but it still is not working.
markros, yours returns a new record in with the exceptions.

c1 c2 c3 c1 c2 c3
----- ----- ----- ----- ----- -----
2 Z A2 NULL NULL NULL
3 D ZZ NULL NULL NULL
8 E A5 NULL NULL NULL
9 Z 6 NULL NULL NULL <-----
4 D ZZ NULL NULL NULL

Borislav, yours returns 1 record on the first query and the new record on the second.

b.c1 b.c2 b.c3 a.c1 a.c2 a.c3
----- ----- ----- ----- ----- -----
1 A A1 1 A A1



Maybe it would be easier to figure out what is new and old and remove them?

This needs to be updated
b.c1 b.c2 b.c3 a.c1 a.c2 a.c3
----- ----- ----- ----- ----- -----
1 A A1 1 A A1

This needs to be added
b.c1 b.c2 b.c3 a.c1 a.c2 a.c3
----- ----- ----- ----- ----- -----
9 Z 6 null null null

The rest are suspect.

Where this is coming from is I get a file refresh each night from our mainframe. It sends it to me as a flat file. I import it into SQL Server and match it.

If it finds an item number then maybe the upc was changed. I can't add it to the table so I place the record into a holding table to be edited by hand.
I match on 3 fields. The item number, the upc and the source with is the state (NJ,CA) that the product is produced in.

I even tried a cursor but it got too complicated and I'm sure there's a join that would do this.

Thanks

 

[bborissov]

I missed the part

only one or two of the fields match

I thought they should be always first two

try:

DECLARE @TableA TABLE (C1 int, C2 CHAR(1),  C3 CHAR(2))
INSERT INTO @TableA VALUES(1,'A','A1')
INSERT INTO @TableA VALUES(2,'B','A2')
INSERT INTO @TableA VALUES(3,'C','A3')
INSERT INTO @TableA VALUES(4,'D','A4')
INSERT INTO @TableA VALUES(5,'E','A5')
INSERT INTO @TableA VALUES(6,'F','A6')

DECLARE @TableB TABLE (C1 int, C2 CHAR(1),  C3 CHAR(2))
INSERT INTO @TableB VALUES(1,'A','A1')
INSERT INTO @TableB VALUES(2,'Z','A2')
INSERT INTO @TableB VALUES(3,'D','ZZ')
INSERT INTO @TableB VALUES(8,'E','A5')
INSERT INTO @TableB VALUES(9,'Z','6 ')
INSERT INTO @TableB VALUES(4,'D','ZZ')


SELECT b.c1 [b.c1],
       b.c2 [b.c2],
       b.c3 [b.c3],
       a.c1 [a.c1],
       a.c2 [a.c2],
       a.c3 [a.c3]
FROM @TableB b
INNER JOIN @TableA a
      on b.c1 = a.c1 and
         b.c2 = a.c2
WHERE b.c3 NOT IN (SELECT C3 FROM @TableA TableA
                          WHERE TableA.C1 = b.C1 AND
                                TableA.C2 = b.C2)

UNION
SELECT b.c1 [b.c1],
       b.c2 [b.c2],
       b.c3 [b.c3],
       a.c1 [a.c1],
       a.c2 [a.c2],
       a.c3 [a.c3]
FROM @TableB b
INNER JOIN @TableA a
      on b.c2 = a.c2 and
         b.c3 = a.c3
WHERE b.c1 NOT IN (SELECT C1 FROM @TableA TableA
                          WHERE TableA.C2 = b.C2 AND
                                TableA.C3 = b.C3)
UNION
SELECT b.c1 [b.c1],
       b.c2 [b.c2],
       b.c3 [b.c3],
       a.c1 [a.c1],
       a.c2 [a.c2],
       a.c3 [a.c3]
FROM @TableB b
INNER JOIN @TableA a
      on b.c1 = a.c1 and
         b.c3 = a.c3
WHERE b.c2 NOT IN (SELECT C2 FROM @TableA TableA
                          WHERE TableA.C1 = b.C1 AND
                                TableA.C3 = b.C3)

For only ONE matching fields you must expand that select


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

And I missed the whole requirement I thought all 3 columns must match.

Anyway, I hope Boris will figure this out.

 

[bigfoot]

I have a solution for all of this! It took a few days of not thinking about it.

Return only the records that match

-- ONLY RETURN EDIT RECORDS
SELECT b.c1[b.c1],b.c2[b.c2],b.c3[b.c3],a.c1[a.c1],a.c2[a.c2],a.c3[a.c3]
FROM TableB b
INNER join TableA a on
      b.c1 = a.c1 and
      b.c2 = a.c2 and
      b.c3 = a.c3

Return only the records that do not match

-- Return only new records
SELECT b.c1[b.c1],b.c2[b.c2],b.c3[b.c3], a.c1[a.c1],a.c2[a.c2],a.c3[a.c3]
FROM TableB b
left outer join TableA a 
on
      b.c1 = a.c1 
and
      b.c2 = a.c2 
and
      b.c3 = a.c3
where
b.c1 not in (select a.c1 from TableA a)
and
b.c2 not in (select a.c2 from TableA a)
and
b.c3 not in (select a.c3 from TableA a)

Return only the records that partially match

SELECT b.c1[b.c1],b.c2[b.c2],b.c3[b.c3], a.c1[a.c1],a.c2[a.c2],a.c3[a.c3]
FROM TableB b
right outer join TableA a 
on
      b.c1 = a.c1 
and
      b.c2 = a.c2 
and
      b.c3 = a.c3
where
b.c1 is null
and
b.c2 is null
and
b.c3 is null

 

[bigfoot]

Thank you all for your help BTW. You got me to think outside the box. that's always good.