MySQL join causing duplicate results

Question

I am trying to get a query out of two tables. This should be straightforward; however, my results duplicate. I can not use DISTINCT or GROUP BY since there is not a unique ID. For testing, my primary table has 87 rows. With no WHERE identified I get 174 results. My two tables are as follows:

table - lifegroups_attendance (date is a date field, time is a varchar)
No unique ID - a combo of date/time/group would be closest to it

date | time | group| attendance
--------------------------------
1/3/15 | 6:30 PM | Adults | 125
1/3/15 | 11:00 AM | Adults | 621
1/4/15 | Saturday | Kids | 289
1/4/15 | Sun PM | Adults | 621

table - sat_week_date_map (the start of the week is Saturday instead of MySQL built in Sun or Mon option)
Date is the primary ID

date | week
---------------
1/3/15 | 1
1/4/15 | 1
1/5/15 | 1
1/10/15 | 2

I need to get a result that looks like this:

week | group | attendance
1    |    adults |   125 
1    |    adults |   621
1    |    adults |   621 (This is not a duplicate - date/time is different)
2    |    adults |   475 
2    |    adults |   276
2    |    kids   |   289

Instead I'm getting

week | group | attendance
1    |    adults |   125 
1    |    adults |   621
1    |    adults |   621 (This is not a duplicate - date/time is different)
2    |    adults |   475 
2    |    adults |   276
2    |    kids   |   289
1    |    adults |   125  (repeats every record over again)
1    |    adults |   621
1    |    adults |   621 (This is not a duplicate - date/time is different)
2    |    adults |   475 
2    |    adults |   276
2    |    kids   |   289

The query I'm currently using is:

SELECT   wd.week, la.group AS group, la.attendance
FROM     sat_week_date_map  AS wd JOIN lifegroups_attendance AS la 
ON    la.date =  wd.date;

I don't understand the question. Your query returns week, date, attendandance, why does your sample result have week, group, attendance? — Barmar
– Barmar, Commented Jan 30, 2015 at 21:39
I don't see where the attendance numbers in your result come from. Ther's no 125 or 621 in the input. — Barmar
– Barmar, Commented Jan 30, 2015 at 21:41
You say the second 1 adults 621 is not a duplicate because the time is different. But shouldn't the attendance column be different at the different times, like in your lifegroups_attendance table? — Barmar
– Barmar, Commented Jan 30, 2015 at 21:43
I've adjusted the query. I've had multiple iterations and removed date at one point and added group just to see what happens. It should be querying week, group, attendance. The data is sample data... the numbers are not accurate. I just threw some in to show the results I'm trying to get. I've edited the original data to now use the same numbers. The attendance numbers in my real number have quite a few duplicates at different times. Yes, it's odd but apparently different service times have the same attendance numbers by chance. Does that help to see what I'm trying to achieve? — Jennifer Miles
– Jennifer Miles, Commented Jan 30, 2015 at 22:20

roeygol · Accepted Answer · 2015-01-30 22:19:02Z

1

You can warp your query with another query and then use distinct like:

SELECT distinct * 
FROM (SELECT   wd.week, la.group AS group, la.attendance
FROM     sat_week_date_map  AS wd JOIN lifegroups_attendance AS la 
ON    la.date =  wd.date)

answered Jan 30, 2015 at 22:19

roeygol

5,07812 gold badges60 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Noyes · Accepted Answer · 2015-01-30 22:43:08Z

0

Check carefully if you have multiple entries for the same date in sat_week_date_map. If there are multiple such entries, they will result in apparently duplicated results.

SELECT date, COUNT(*) FROM sat_week_date_map GROUP BY date HAVING COUNT(*) > 1;

answered Jan 30, 2015 at 22:43

Scott Noyes

3731 silver badge9 bronze badges

Collectives™ on Stack Overflow

MySQL join causing duplicate results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related