MySQL operation guide using UNION and LIMIT

Question

So, I have this problem at hand.

I have to get multiple records from different tables using inner joins and using UNION or UNION ALL for pagination, the following code is for getting all records and count. This is my nemesis:

(SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join inn_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE (pp.type = 1 or pp.type = 2) 
        $where ) 
       UNION 
        (SELECT pp.id as id, pp.*, i.model as name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join rent_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 3
        $where) 
       UNION 
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join fish_pay_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 4
        $where ) 
       UNION 
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join stores_oficial i ON i.photo_id = pp.property_id
        left join plans p ON pp.plans_id = p.id
        WHERE pp.type = 5
        $where)
       UNION
        (SELECT pp.id as id, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
        inner join users u ON pp.users_id $username
        inner join transfer_oficial i ON i.photo_id = pp.property_id
        inner join plans p ON pp.plans_id = p.id
        WHERE pp.type = 6
        $where)
        GROUP BY pp.id
        ORDER BY pp.id

I took this example from the 13.2.9.3 UNION Clause and tried it on a script

When I try to run the code, it reports an error

There is something wrong with your SQL syntax next to 'GROUP BY id ORDER BY id LIMIT 0.25' on line 1

Sorry, my English is terrible, this is not my native language

Answer 1

I solved kskskskks, just add the pp id column as id_p and then ordered by that

(SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join inn_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE (pp.type = 1 or pp.type = 2) 
    $where ) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.model as name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join rent_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 3
    $where) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join fish_pay_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 4
    $where ) 
   UNION 
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join stores_oficial i ON i.photo_id = pp.property_id
    left join plans p ON pp.plans_id = p.id
    WHERE pp.type = 5
    $where)
   UNION
    (SELECT pp.id as id_p, pp.*, i.name, p.name as plans_name, u.username,u.email,u.phone, DATE_ADD(pp.init_date, INTERVAL pp.avaliable_days DAY) as end_date FROM property_purchase pp
    inner join users u ON pp.users_id $username
    inner join transfer_oficial i ON i.photo_id = pp.property_id
    inner join plans p ON pp.plans_id = p.id
    WHERE pp.type = 6
    $where)
    ORDER BY id_p $pagination