INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return only duplicate records

Return only duplicate records

(OP)
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!

RE: Return only duplicate records

I would have thought something along these lines would work
[code]
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 winky smile

Andrew

RE: Return only duplicate records

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

How about

CODE

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

RE: Return only duplicate records

(OP)
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.

RE: Return only duplicate records

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

CODE

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

RE: Return only duplicate records

(OP)
Many thanks!

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close