1

I have a list of events and I would like to find duplicates for upcoming events. Since the same event can have several different event titles, I think the safest bet here is to look for events that happen in the same venue same day and remove duplicates manually from there.

Similar question was asked here.

$todays_date = date('Y-m-d');   
$sql="         
SELECT place_name, event_title, start_date, count(*)
FROM main WHERE start_date  >= '$todays_date'
GROUP BY start_date, place_name HAVING count(*) > 1";
    $result = mysql_query($sql);
    //display events
    while ($row = mysql_fetch_array($result)) 
        {
            echo $row['place_name'] . "- ";
            echo $row['event_title'] . "- "; 
            echo $row['start_date'] . "<br/>"; 
        }

This does not give me an expected result at all, in fact, I have no idea how this recordset is assembled.

Venue - Event Name - Date

Rotary Centre for the Arts- Leela Gilday - 2017-03-10
Rotary Centre for the Arts- Joe Trio - 2017-03-21
Vernon and District Performing Arts Center- Shay Keubler's GLORY- 2017-04-01
Vernon and District Performing Arts Center- A Midsummer Night's Dream- 2017-04-30
Vernon and District Performing Arts Centre- Canadiana Suite - 2017-05-06
Kelowna Community Theatre - Glenn Miller Orchestra- 2017-06-27

Any tips are much appreciated.

I tried it without the date, and just grouping by event_title and place_name, with a similar strange output where the only duplicates are those of the venue names (place_name).

Community
  • 1
  • 1
Natalia
  • 417
  • 3
  • 7
  • 18

2 Answers2

3

When you use GROUP BY, you only get one row for each group. The other columns that you select will be randomly selected from within the group.

If you want to see all the rows that are in the duplicate groups, join the table with the query that finds the duplicates.

SELECT t1.*
FROM main AS t1
JOIN (SELECT place_name, start_date
      FROM main 
      WHERE start_date  >= '$todays_date'
      GROUP BY start_date, place_name 
      HAVING count(*) > 1) AS t2
ON t1.place_name = t2.place_name AND t1.start_date = t2.start_date
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • marked up for the explanation, the results I get are better, but they are still not displayed "in pairs", a duplicate after duplicate – Natalia Jan 20 '17 at 02:20
  • ok I added `ON t1.place_name = t2.place_name AND t1.start_date = t2.start_date ORDER BY start_date`, looks good now! – Natalia Jan 20 '17 at 02:32
0

try this:

select main_count,a.place_name,b.event_title, b.start_date from
(select count(1)as main_count, place_name from main WHERE start_date  >= '".$todays_date."' group by place_name having count(1) >1) as a

left join

(select  place_name, event_title, start_date from main) as b
on a.place_name = b.place_name
Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
  • If startdate fails on first query then add it.. try to run it first to your database not in code to simulate the problem and could get a good result before your proceed to your project code. good luck – Vijunav Vastivch Jan 20 '17 at 01:49
  • Why use `LEFT JOIN` instead of `INNER JOIN`? There are always going to be matches. – Barmar Jan 20 '17 at 02:02
  • Thanks @Barmar its the same but i need to be more specific the second query must depend on the main which is the first query. – Vijunav Vastivch Jan 20 '17 at 02:12
  • You're not joining on the correct columns, you need to join on both `place_name` and `start_date`. Otherwise you'll join together events that are on different days. – Barmar Jan 20 '17 at 16:46
  • Yeah its true i didn't include the date in where clause. thanks Guru – Vijunav Vastivch Jan 24 '17 at 00:55