CODEWITHSUNDEEP
mysql
Jennifer Miles
Jennifer Miles

Reputation: 11

MySQL join causing duplicate results

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;

Upvotes: 0

Views: 117

Answers (2)

Scott Noyes
Scott Noyes

Reputation: 373

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;

Upvotes: 0

roeygol
roeygol

Reputation: 5028

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)

Upvotes: 1

Related Questions