Re: Returning All Rows That Repeat

John Kopanas wrote:
> I want to be able to return all rows that have one or more other rows
> with the same customer_number and job_number.
>
> So for instance. If their are two jobs in my jobs table with a
> customer_number = '0123' and job_number ='12' then I want both of
> those jobs to return one right after another so I can compare their
> other fields. And I want to run a query once a day over the whole
> table to see if their are any repeats.
>
> Does anyone have a clue how to do this?
>
> Thanks :-)
>
> Your Friend,
>
> John
>
>

Not sure how to do this with one command right now because i'm tired and cant think straight but heres one way:

create temporary table tempjobdupes as select customer_number, job_number from jobs group by customer_number, job_number having count(*) >1;
select * from jobs where (customer_number, job_number) = any (select * from tempjobdupes);
drop table tempjobdupes;

theres got to be another way but the subqueries i'm trying aren't working how i want. If i think of anything else i'll try again. Let me know how this works for you.

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com