August 25, 2004 at 6:02 am
Hello.
I have this table, table1, that looks like this:
inv_tag rm_id audit_status
Where the inv_tag column will have duplicate values. Eg:
1 495 Audited
1 356 Not Audited
2 485 Not Audited
2 485 Audited
3 754 Not Audited
3 857 Audited
The Audited value is from a audit of inventory. I need to be able to restrict the selection to only those inv_tag that are equal. There are no PK's in the table. The thing we are verifying is the location of the specific inventory items (inv_tag), and if they are correct or not. E.g., inv_tag 1 is not correct, since the room number is different on the audit than what it was in the current inventory table.
Please let me know if you need more information.
Thank you.
August 26, 2004 at 2:32 am
Hello,
You can try the following :
select inv_tag, count(distinct rm_id)
from table1
group by inv_tag
having count(distinct rm_id) > 1
Bye...
August 26, 2004 at 5:18 am
Thank you for the suggestion Jean-Pierre.
I found another solution that gave me the intended results:
select tbl1.*
from table1 tbl1, table1 tbl2
where tbl1.inv_tag=tbl2.inv_tag
and tbl1.rm_id<>tbl2.rm_id
order by tbl1.inv_tag
Thanks again!
August 26, 2004 at 8:00 am
select * from table1
where inv_tag in (
select inv_tag from table1
group by inv_tag
having count(distinct rm_id) > 1)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply