Jump to content

Getting unique rows


ginerjm
 Share

Recommended Posts

Need a little help with this query.  A bit complex and definitely at the limit of my abilities since I don't write that many queries that often and not usually this tricky.

Here's the query with the output from it next:

SELECT d.Drill_date, q.Roster_no, q.drills 

	FROM MMS_Drills d 
    
    LEFT OUTER JOIN (
        	select a.Roster_no, t.Drill_date, count(a.Drill_no) as drills
                     	FROM MMS_Drillatt a, MMS_Drills t 
                        WHERE a.Drill_no = t.Drill_no and 
                     		YEAR(t.Drill_date) = '2022' AND 
                            t.Description Not Like '%National%'  
                    	GROUP BY a.Roster_no, t.Drill_date 
    				) q 
	ON q.Drill_date = d.Drill_date 
    
	WHERE YEAR(d.Drill_date) = '2022' AND 
    	d.Description Not Like '%National%' AND 
        q.drills > 1 
    
    ORDER BY Drills DESC, d.Drill_date, q.Roster_no

 Output:  (wouldn't post into a 'code' box)

Drill_date  Roster_no  drills

2022-04-26   175     2

2022-04-26   175     2

2022-04-26   220     2

2022-04-26   220    2

What I would like to do is eliminate these duplicate rows.  The data is accurate, just my output is more than I want.

Any ideas?  I realize that this is perhaps too much to solve without knowing more but perhaps there is a simple solution that a more experienced query writer knows.

Link to comment
Share on other sites

Tip: with this sort of query+subquery setup, if you have a GROUP BY x, y, z in the inner query then you should probably be joining on x, y, and z in the outer query. Otherwise you'll get duplicates.

So here, the inner query searches by roster_no and drill_date, but outside you're only considering drill_date. That means there could be "duplicate" rows where the drill_date is the same but the roster_no is different.
That may manifest differently depending on the query, but one way or another if there are duplicate rows on the inside then you'll get duplicate rows on the outside.

Link to comment
Share on other sites

There is quite a bit of data on file, so I kinda can't.  Is there something specific you are interested in, or just the total structure.  I can understand if you want to pursue it deeper.  As I said though - the query is correct.  I just want to clean it up via query, rather than using php, if that is possible.

Link to comment
Share on other sites

2 minutes ago, ginerjm said:

the query is correct.

If it were you wouldn't be getting those duplicate rows.

Can you send the sql to create the tables then, and I'll have to make up some some test data to get the result you get. (There's nothing like having to do extra work to help someone.)

Link to comment
Share on other sites

Here are the structures.  If that is not sufficient, please don't make that extra effort.  I completely understand.  I just was hoping that someone like you knew something that I didn't.

MMS_Drills

Drill_no, Drill_date, Description

MMS_Drillatt

Drill_no, Roster_no

The tables represent my firehouse drill records - 1 per drill and the attendance to each drill, multiple naturally. 

What is happening is recently we now have 2 drills per night where people are supposed to sign for one or the other, but not both.  I'm trying to search for those bad instances where they do sign both.  So I'm querying for counts of guys who sign 2 drills with the same date.

Does this help?

Link to comment
Share on other sites

Posted (edited)

I really didn't expect this kind of trouble for anyone.  Just thought I was missing something simple.  As for test data, the sample of my 'problem' should be sufficient if you add a 2nd row to each drillatt with a new drill_no value, ie, 2 drill nos having the same date in the drills table and 2 attendance records for each roster with different drill nos.

Sleep well!

Edited by ginerjm
Link to comment
Share on other sites

Does this give what you need?

SELECT roster_no
     , drill_date
     , count(distinct drill_no) as drills
FROM mms_drills
     JOIN
     mms_drillatt USING (drill_no)
WHERE YEAR(drill_date) = '2022' AND 
     description Not Like '%National%'
GROUP BY roster_no, drill_date     
HAVING drills > 1;

 

Link to comment
Share on other sites

4 hours ago, ginerjm said:

people are supposed to sign for one or the other, but not both.  I'm trying to search for those bad instances where they do sign both.

Seems to me this is the real problem. Stop them from signing both and you wont have to fix it afterwards.

Link to comment
Share on other sites

Benanamen - wonderful idea but the guys will do it anyway.  It's not like we stand around the clipboard when drill is over.  Yes - sometimes it is easily doable when there are only 2-3 guys on one sheet and all the rest on the other.  But then it happens.

Barand - You Da Man!  Must of stayed up late or arose early and had nothing to do but look at my problem.  Your query worked perfectly.  And with the change below it made my query work as well.  Although I like yours better.  I need to learn your style/fashion but this old dog really doesn't learn new tricks quickly.

WHERE YEAR(d.Drill_date) = '2022' AND
    	d.Description Not Like '%National%' 
	GROUP BY roster_no, drill_date     
	HAVING drills > 1	

    ORDER BY Drills DESC, d.Drill_date, q.Roster_no
	

Changing the bottom of my original query makes it work.

Link to comment
Share on other sites

After writing my version I compared it to yours and noticed it was almost identical to your subquery.

Your version only needed the query you have in the subquery (+ HAVING clause + ORDER BY). The rest was completely superfluous.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.