- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: sql query assistance
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-29-2004 09:53 PM
тАО04-29-2004 09:53 PM
sql query assistance
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-29-2004 10:32 PM
тАО04-29-2004 10:32 PM
Re: sql query assistance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-30-2004 12:22 AM
тАО04-30-2004 12:22 AM
Re: sql query assistance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-30-2004 12:32 AM
тАО04-30-2004 12:32 AM
Re: sql query assistance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2004 07:17 PM
тАО05-02-2004 07:17 PM
Re: sql query assistance
union
select 'T1', * from t1 minus select* from t2
union
select 'T2', * from t2 minus select * from t1;
HTH
Art
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2004 07:52 PM
тАО05-02-2004 07:52 PM
Re: sql query assistance
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2004 10:51 PM
тАО05-02-2004 10:51 PM
Re: sql query assistance
select * from t1
where rowid!=(select t1.rowid
from t1,t2
where t1.id=t2.id);
will the above query help?
best regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-27-2004 08:09 PM
тАО05-27-2004 08:09 PM
Re: sql query assistance
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-28-2004 10:54 AM
тАО05-28-2004 10:54 AM
Re: sql query assistance
(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