cancel
Showing results for 
Search instead for 
Did you mean: 

sql query assistance

Edgar_8
Regular Advisor

sql query assistance

Hi,

We have 2 tables, identical in structure, what we need is a query that selects 2 or more columns from table1
& compares these values to the same columns of table2. The result sets/sql queries must show matching data
& data in table1 thats not in table2 and vice versa. Anyone has any ideas on what the sql query should be
like?

Thanks in advance!
8 REPLIES
Volker Borowski
Honored Contributor

Re: sql query assistance

Hi,

assuming you mean to compare data of rows with the same key :

Statement to show all diffrent fields for same keys

select a.f1,b.f1,a.f2,b.f2 ...
from table1 a, table2 b
where
a.keyfield1=b.keyfield1
and a.keyfield2=b.keyfield2
... (continue) for all keyfileds
and
(
a.f1 <> b.f1
or a.f2 <> b.f2
... (continue) for all datafields to be compared
);


Statement to show the "missing" keys from the other table should be easysly implementable with the MINUS optin, but I never tried that. According to some google hits it should go like

SELECT * from T1 MINUS SELCT * from T2;

But I never tried that and have no sample available right now.

Volker

Sanjay Kumar Suri
Honored Contributor

Re: sql query assistance

Check the following

select f1, f2
from table1
where ...
minus
select f1, f2
from table2
where ...

The above will find the row from the first query, but not in the second query.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Jean-Luc Oudart
Honored Contributor

Re: sql query assistance

tables content :
SQLWKS> select * from T1
2>
COL1 COL2 COL3
---------- ---------- ----------
abcd abcd 10
abce abce 11
abcd abce 12
xxxx yyyy 12
4 rows selected.
SQLWKS> select * from T1
2> intersect
3> select * from T2
4>
COL1 COL2 COL3
---------- ---------- ----------
abcd abcd 10
abcd abce 12
abce abce 11
3 rows selected.

matching data :
SQLWKS> select * from T1
2> intersect
3> select * from T2
4>
COL1 COL2 COL3
---------- ---------- ----------
abcd abcd 10
abcd abce 12
abce abce 11
3 rows selected.

difference T1 vs T2 :
SQLWKS> select * from T1
2> minus
3> select * from T2
4>
COL1 COL2 COL3
---------- ---------- ----------
xxxx yyyy 12
1 row selected.

difference T2 vs T1
SQLWKS> select * from T2
2> minus
3> select * from T1
4>
COL1 COL2 COL3
---------- ---------- ----------
0 rows selected.


Regards,
Jean-Luc
fiat lux
Arturo Galbiati
Esteemed Contributor

Re: sql query assistance

select 'T1&T2', * from t1 interset select* from t2
union
select 'T1', * from t1 minus select* from t2
union
select 'T2', * from t2 minus select * from t1;

HTH
Art
Antoniov.
Honored Contributor

Re: sql query assistance

To match different record with same key
SELECT * FROM T1,T2
WHERE
(T1.KEY = T2.KEY)
AND T1.FIELD1 <> T2.FIELD1
AND T1.FIELD2 <> T2.FIELD2
...
ORDER BY T1.KEY

Above query doesn't match record exists in T1 and doesn't exist in T2 or viceversa.

@Antoniov
Antonio Maria Vigliotti
Yogeeraj_1
Honored Contributor

Re: sql query assistance

hi,

select * from t1
where rowid!=(select t1.rowid
from t1,t2
where t1.id=t2.id);

will the above query help?

best regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Mathan_1
Occasional Visitor

Re: sql query assistance

select colmn1, colmn2 from table1 as t1 inner join table2 as t2 on t1.column1=t2.column2
Brian Crabtree
Honored Contributor

Re: sql query assistance

select c1,c2 from table1 A where not exists
(select 1 from table2 B where a.c1 = b.c1 and a.c2 = b.c2)
union
select c1,c2 from table2 B where not exists
(select 1 from table1 A where a.c1 = b.c1 and a.c2 = b.c2)
/

Let me know if this is what you are looking for. It will only display data from table1 and table2 that do not exist in the other.

Thanks,

Brian