topic Re: sql query assistance in Operating System - HP-UX

sql query assistance

Edgar_8 — Fri, 30 Apr 2004 04:53:31 GMT

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!

Re: sql query assistance

Volker Borowski — Fri, 30 Apr 2004 05:32:19 GMT

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

Re: sql query assistance

Sanjay Kumar Suri — Fri, 30 Apr 2004 07:22:42 GMT

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

Re: sql query assistance

Jean-Luc Oudart — Fri, 30 Apr 2004 07:32:06 GMT

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

Re: sql query assistance

Arturo Galbiati — Mon, 03 May 2004 02:17:53 GMT

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

Re: sql query assistance

Antoniov. — Mon, 03 May 2004 02:52:38 GMT

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

Re: sql query assistance

Yogeeraj_1 — Mon, 03 May 2004 05:51:05 GMT

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

Re: sql query assistance

Mathan_1 — Fri, 28 May 2004 03:09:55 GMT

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

Re: sql query assistance

Brian Crabtree — Fri, 28 May 2004 17:54:32 GMT

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