From: gelangov@hotmail.com (Geetha)
Newsgroups: comp.databases.oracle
Subject: EXISTS
Date: 7 Jul 2003 10:31:42 -0700
Organization: http://groups.google.com/
Lines: 24
Message-ID: <4b40e20a.0307070931.61de4f0e@posting.google.com>
NNTP-Posting-Host: 65.168.239.34
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1057599103 31217 127.0.0.1 (7 Jul 2003 17:31:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 7 Jul 2003 17:31:43 GMT


I want to know the column names that exists in more than one table;
also, I want to know the table names where it exists. I tried this
query and it took a long time and so I stopped it:

select table_name,column_name, data_length, data_type from
user_tab_cols
where column_name in  (select column_name from user_tab_cols
group by column_name
having count (*) > 1)
order by column_name

When I try using EXISTS instead of IN, I get wrong results; it gives
me the the column names even when it exists in only one table.
select table_name,column_name, data_length, data_type from
user_tab_cols
where exists  (select column_name from user_tab_cols
group by column_name
having count (*) > 1)
order by column_name

Please let me know what I am doing wrong?  I am not very familiar with
EXISTS

Thank you in advance.