CODEWITHSUNDEEP
mysqlgroup-byduplicates
Natalia
Natalia

Reputation: 417

find mySQL results that are duplicates in two columns - GROUP BY doesn't work

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

Upvotes: 1

Views: 408

Answers (2)

Barmar
Barmar

Reputation: 781721

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

Upvotes: 3

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

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

Upvotes: 0

Related Questions