Return only duplicate records 1

[TrinityD]

Hi,

I am trying to write a query for a diary application that manages engineers and I am totally stuck on something that I think should really be relatively easy so looking for help. I've been going round in circles with this today and just can't get it right. Basically, I'm trying to run a query that would display any jobs where more than one engineer is visiting the same street on the same day, unless they were assisting each other with the same job.

The relevant columns are:

multiplejob (unique to each job, so assists or multiple slots on the same job will have the same string in this column)
engineer
customer_street
job_date

Any help to point me in the right direction is greatly appreciated!

 

[towerbase]

I would have thought something along these lines would work

SELECT customer_street
     , job_date
     , COUNT(*) AS jobs
FROM table
GROUP BY customer_street, job_date
HAVING jobs > 1
[code]
but I haven't tested it so there may be typos ;-)



Andrew

 

[towerbase]

Sorry, my answer doesn't provide you with what you've asked for!

How about

SELECT GROUP_CONCAT(multiplejob)
     , customer_street
     , job_date
     , COUNT(*) AS jobs
FROM table
GROUP BY customer_street, job_date
HAVING jobs > 1

That will provide a comma separated list of jobs where two or more jobs are booked out to the same street and date.


Andrew

 

[TrinityD]

Hi,

Many thanks for your reply.

Your query pulls out any multiple jobs on the same street, but doesn't differentiate whether it is the same engineer visiting or different engineers. This is the part I have been struggling with. To be more clear, I only need the query to return a result if there is more than one engineer on the same street, for different jobs. So for example, Engineer 1 is visiting 10 test street tomorrow, but engineer 2 is visiting 9 test street on the same day.

So basically I pass a date to this script and it needs to return whether the diary is ok for that day or whether there are potential problems.

Just to be clear, I am not expecting anyone to write everything for me, I am just needing pointed in the right direction as no matter what I try to do, I can't get the logic in my head for checking whether there is more than one engineer on the same day/street.

Thanks in advance.

 

[towerbase]

I think you need to do a self join along the lines of

SELECT a.multiplejob
     , a.engineer
     , b.multiplejob
     , b.engineer
FROM tablename a
JOIN tablename b ON a.customer_street=b.customer_street
                AND a.jobdate=b.jobdate
                AND a.multiplejob < b.multiplejob

This selects the multiplejob and engineers of the jobs where the street is the same, the jobdate is the same but the multiplejob is different.


Andrew

 

[TrinityD]

Many thanks!

You've pointed me in the right direction and with a little tweaking, I think I have a working solution.

SELECT a.multiplejob
     , a.engineer
     , a.customer_street
     , b.multiplejob
     , b.engineer
     , b.customer_street
FROM jobs a
JOIN jobs b ON a.customer_street=b.customer_street
                AND a.multiplejob < b.multiplejob 
                WHERE a.job_date = '2013-02-11' 
                AND b.job_date = '2013-02-11'
                AND a.engineer != b.engineer

I'll need to test/tweak it to make sure it's full working but at least I am going in the right direction now.

Many thanks, it is much appreciated.