| SQL [message #9783] |
Thu, 04 December 2003 11:32  |
Gopal
Messages: 23
Registered: June 1999
|
Junior Member |
|
|
Hi
I have a problem, i have 2 tables (dist_planids and odsplanids) both these tables contain only one field i.e., PLANID. table1 contains 1850 PLANIDS and table2 contains 1540 planids. I have to generate a list of PLANIDS which are only there in table1 and i also have to generate a list of PLANIDs which are there only in TABLE2. if a planid is there in both the tables we can ignore it.
Can anyone help me in writing this SQL QUERY?
Thanks in Advance
Gopal
|
|
|
|
| Re: SQL [message #9784 is a reply to message #9783] |
Thu, 04 December 2003 12:37  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
SELECT planid FROM table1 A WHERE NOT EXISTS
(SELECT'x' FROM table2 WHERE planid=a.planid);
SELECT planid FROM table2 A WHERE NOT EXISTS
(SELECT'x' FROM table1 WHERE planid=A.planid);
|
|
|
|
| Re: SQL [message #9791 is a reply to message #9783] |
Thu, 04 December 2003 17:06 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Or try
-- dist_planids but not odsplanids:
SELECT planid FROM dist_planids
MINUS
SELECT planid FROM odsplanids;
-- odsplanids but not dist_planids:
SELECT planid FROM odsplanids;
MINUS
SELECT planid FROM dist_planids
-- If you do ever need it, planids that are only in both tables:
SELECT planid FROM odsplanids;
INTERSECT
SELECT planid FROM dist_planids
|
|
|
|
| Re: SQL [message #9801 is a reply to message #9791] |
Fri, 05 December 2003 02:08 |
Reema
Messages: 50
Registered: July 2003
|
Member |
|
|
You can use this also
Select planid from dist_planids
UNION
Select planid from odsplanids
I assume that the datatypes are same in both.
Union will result only the non-Duplicate rows.
Doing a Minus mighht be a problem in the following scenarion;
Table A has values
1,2,3,4,5
Table B has values 2,3,7,8
so in this case minus of the select on two tables wll return
1,4,5
where as I guess you want the result to be
1,2,3,4,5,7,8
Any doubts you can revert back.
|
|
|
|
| Re: SQL [message #9812 is a reply to message #9801] |
Fri, 05 December 2003 06:01 |
Dave
Messages: 92
Registered: August 1999
|
Member |
|
|
Might this not be simpler?
Only in Table1
SELECT PLANID FROM TABLE1 WHERE PLANID NOT IN (SELECT PLANID FROM TABLE2)
Only in Table2
SELECT PLANID FROM TABLE2 WHERE PLANID NOT IN (SELECT PLANID FROM TABLE1)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
