compare two tables with three columns as key 1

[lyudmila]

I have two tables TBL1 and TBL2. Both have three key columns (they are not PK and FK, we have identity column asigned by the data load tool as PK ) Since all rows distinct because of identity column, What is the best way to check if it is one-to-one relationship between this two tables on key columns

TBL1/ VEN_NO, PO_NO, INV_NO, f4, f5, f6

TBL2/ VEN_NO, PO_NO, INV_NO, f4, f5, f6, f7, f8, f9

 

[MeanGreen]

Lets say your "key columns" are f4, f5, and f6. You could do this:

select tbl1.*,"Tbl1 not Tbl2" from tbl1 left join tbl2
on tbl1.f4 = tbl2.f4
and tbl.f5 = tlb2.f5
and tbl.f6 = tbl2.f6
where tbl2.f4 is null
union
select tbl2.*,"Tbl2 not Tbl1" from tbl1 Right join tbl2
on tbl1.f4 = tbl2.f4
and tbl.f5 = tlb2.f5
and tbl.f6 = tbl2.f6
where tbl1.f4 is null


This would give you a listing showing where 1 table has values that the other table doesn't.

Hope this helps.

 

[SQLSister]

Are you really far along in your development? If not, I would consider changing your data structure.
Table one
IDField, VEN_NO, PO_NO, INV_NO, f4, f5, f6
Table two
IDField, f4, f5, f6, f7, f8, f9

I would link on the identity field in table one rather than the three key fields. This way, you don't keep having to write these complicated joins, the join will be faster, and you'll save space in the database by not repeating the data from VEN_NO, PO_NO, INV_NO in both tables. Less likely to have a data integrity issue too. What you lose is that you will have to do a join to table one to see the information on these fields with the data in table 2. This is more than made up for by the ease of programming and the increased speed. You probably don't look at table two data by itself all that often anyway. (Oh yeah, I'd put a differnt identity column of its own in table two just because I never create a table without one.)

I can see times when you might want to repeat the data (we do with the parts table and the proposal items table, because a part used in a proposal might become obsolete and disappear from the parts table, but it was still the one we priced the proposal on). But if you don't need to have the data in both places, I would avoid doing so.

 

[lyudmila]

Hi,
I cannot change anything in data structure, this is out of question!
After I run union, I got this message "All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.' My tables have different number of columns, but I select only key columns?
What about this syntax:

SELECT DISTINCT VNDNBR,INVNBR, DIVORG FROM TBL1 A
WHERE NOT EXISTS
(SELECT * FROM TBL2 B WHERE
A.VND_NO=B.VND_NO
and A.INV_NO=B.INV_NO
and A.DEPT=B.DEPT)
-- returns 303104 records

 

[SQLSister]

If you used the union statement above, it would have given that error because it select * (or all columns) did you specify just the three columns you want and get the same error?

Suggest you do a query with an outer join on each of the three fields and then the where cluase would specify that the table two fields are null. I need to go now,, so can't come up with the exact syntax and test it for you, but it is something to think about.

 

[lyudmila]

I need to correct my syntax:

SELECT DISTINCT VND_NO,INV_NO, DEPT FROM TBL1 A
WHERE NOT EXISTS
(SELECT * FROM TBL2 B WHERE
A.VND_NO=B.VND_NO
and A.INV_NO=B.INV_NO
and A.DEPT=B.DEPT)
-- returns 303104 records

IS SOME THING WRONG WITH THIS SYNTAX?
I CHECKED FEW RECORDS, IT SEEMS TO WORK? IS IT INTELLEGENT SOLUTION? PLEASE HELP, I NEED ASAP1

 

[MeanGreen]

Knowing you want only the relative columns, you could try this:


select tbl1.VEN_NO, tbl1.PO_NO, tbl1.INV_NO,"Tbl1 not Tbl2" from tbl1 left join tbl2
on tbl1.VEN_NO = tbl2.VEN_NO and
tbl1.PO_NO = tbl2.PO_NO and
tbl1.INV_NO = tbl2.INV_NO
where tbl2.VEN_NO is null
union
select tbl2.VEN_NO, tbl2.PO_NO, tbl2.INV_NO,"Tbl2 not Tbl1" from tbl1 Right join tbl2
on tbl1.VEN_NO = tbl2.VEN_NO and
tbl1.PO_NO = tbl2.PO_NO and
tbl1.INV_NO = tbl2.INV_NO
where tbl1.VEN_NO is null


Hope this helps.

 

[lyudmila]

Hello MeenGreen,
It worked , when I removed "Tbl1 not Tbl2" , thank you very much for your help.
Where it came from "Tbl1 not Tbl2", I never had seen such syntax?