Getting unique rows

[ginerjm]

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.

[requinix]

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.

[ginerjm]

That is exactly what is happening.  And you are saying that I have to eliminate the dupes programmatically?

[Barand]

Any chance of a dump of the test tables for that query?

[ginerjm]

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.

[Barand]

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

[ginerjm]

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?

[Barand]

Better than nothing - just

I'll see what I can do. It's after midnight here so it'll be tomorrow, so don't hang around.

[ginerjm]

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 May 25, 2022 by ginerjm

[Barand]

4 minutes ago, ginerjm said:

the sample of my 'problem' should be sufficient.

As in

Test data : that data for which the query works

[Barand]

What are the keys on those table?

[Barand]

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;

 

[benanamen]

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.

[ginerjm]

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.

[Barand]

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.