Can not join two tables using group and having functions 1

[tomadobro]

I am trying to find duplicate items in a table. The problem is that the information I need is in more than 1 table. Example of code that works, but does not give me everything I want:

select number
from table
group by number
having count(*) > 1;

Besides the numbers that are duplicated appearing, I need a description to go along with it and that information is in another table. If someone can help me, I would much appreciate it. Thanks.

 

[jjonesal]

Would this work???

select x.number, y.description
from table1 x inner join table2 y
on x.joinfield = y.joinfield
where x.number in (select number from table1
group by number
having count(*) > 1) J. Jones
jjones@cybrtyme.com

 

[Tim1]

Hi,

How about,

SELECT a.number,
b.name,
count(*)
FROM table_1 a,
table_2 b
WHERE a.number = b.number
GROUP BY a.number,
b.name
HAVING COUNT(*) > 1

Tim

 

[tomadobro]

All right, I tried both statements and got errors. Most likely my fault. Let me give you the exact table information and give you a feel for what I'mm trying to do:

select inventory.catalogcode, item.description
from itasca.inventory, itasca.item
group by catalogcode
having count(*) > 1;

Tim1
I tried to fill in the blanks as best as I could and my computer froze, twice.

jjonesal
I tried yours as well and just got an error at the select statement.

Any other suggestions?

 

[Tim1]

Hi,

I think what you need is :

select inventory.catalogcode, item.description
from itasca.inventory, itasca.item
where inventory.catalogcode = item.catalogcode
group by inventory.catalogcode, item.description
having count(*) > 1;

Hopefully that should work,

Tim

 

[tomadobro]

Tim

Thanks for all your help so far, but if you can believe, I'm still getting an error. Here it is:

select inventory.catalogcode, item.description
^
Error: ORA-00904: invalid column name

I checked and rechecked and the column names. They are valid. So, now I'm confused and frustrated again. If yo have any other suggestions, I'll take them. Thanks again.

 

[BG12424]

Will this work similar to an update statement when updating records based on duplicate records? To see what I mean, please go to my posting at

thread183-415456

select x.number, y.description
from table1 x inner join table2 y
on x.joinfield = y.joinfield
where x.number in (select number from table1
group by number
having count(*) > 1)
regards,
Brian