PHP - Moved: Join Statement

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308855.0

The following query selects the rows that I want to delete-

select bad_rows.*
     from products as bad_rows
      inner join (
       select pid, MAX(last_updated_date) as maxdate
          from products
           group by pid
             having count(*) > 1
          ) as good_rows on good_rows.pid= bad_rows.pid
            and good_rows.maxdate <> bad_rows.last_updated_date;

Now to actually delete them I tried changing the first word - 'select' to 'delete', but in this case the query seems to run forever and no rows are deleted. Why ?   How else can these rows be deleted?   btw what I am trying to do is get rid of duplicates. the selelct query works just fine in selecting the records, all I have to do is figure out a way of deleteting them Thanks
Edited by kutchbhi, 14 May 2014 - 01:24 PM.

Hi,

I want to restructure my database tables so that I can have one table (t_incidents) to hold foreign keys instead of holding foreign keys in the table "t_persons" because one person can commit more than one offense.

However, I need to know how the join should be implemented with MySQLi prepared statement.

I need some one to review the following statement for me and advise:

if ($stmt = $mysqli->prepare("Select t_persons.PersonID    
                                                                    
        ,t_persons.FamilyName
        ,t_persons.FirstName
        ,t_persons.OtherNames
        ,t_persons.Gender
        ,t_persons.CountryID
        ,t_persons.ImagePath    

        ,t_incidents.IncidentID
        ,t_incidents.Incident
        ,t_incidents.IncidentDate
        ,t_incidents.PersonID
        ,t_incidents.CountryID
        ,t_incidents.OffenceKeywordID
        ,t_incidents.StatusID
        ,t_incidents.AgencyID

        ,t_status.StatusID
        ,t_status.Status

        ,t_offencekeyword.KeywordID
        ,t_offencekeyword.Keyword

        ,t_countries.CountryID
        ,t_countries.Country

        ,t_agencies.AgencyID
        ,t_agencies.Agency

From t_persons
    Inner Join t_incidents On t_persons.PersonID = t_incidents.PersonID
    Inner Join t_incidents On t_countries.CountryID = t_incidents.CountryID
    Inner Join t_incidents On t_status.StatusID = t_incidents.StatusID
    Inner Join t_incidents On t_offenceskeyword.KeywordID = t_incidents.KeywordID
    Inner Join t_incidents On t_agencies.AgencyID = t_incidents.AgencyID

Where t_persons.PersonID = '$PersonID'")) {

Regards.

josephbupe

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=332358.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=320476.0