CODEWITHSUNDEEP
mysqlsql
programmingnewb
programmingnewb

Reputation: 129

Combing results from union all into one row when some columns have different values

I'm using union all to combine the results of two select statements. I'm trying to group the results by the date_requested column. I need to combine both queries into one row if the dates are the same. Right now, it's keeping two rows for the same date. This is because the tracking_id column is different between the two rows. Since I'm not worried about tracking_id for this, how do I bypass this and combine them anyway? Thanks.

(select trv.requested_date, trv.requested_status
from  tbl_trackvalue as trv ,tbl_tracking as t , tbl_offers as off , tblusers as usr
where t.id=trv.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and trv.payment_status='pending' and trv.requested_status='declined'  group by trv.tr_user_id, trv.requested_date order by trv.requested_date asc )
union all 
(select mlc.requested_date, mlc.requested_status
from  tbl_trackvalue as trv ,tbl_tracking as t , tbl_offers as off , tblusers as usr, tbl_mailchimp_trackvalue as mlc
where trv.tracking_id=mlc.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and mlc.payment_status='pending' and mlc.requested_status='declined'  group by trv.tr_user_id, mlc.requested_date order by mlc.requested_date asc )

UPDATED CODE:

select requested_date, requested_status
from (select trv.requested_date as requested_date, trv.requested_status as requested_status
        from  tbl_trackvalue as trv ,tbl_tracking as trk , tbl_offers as off , tblusers as usr
        where trk.id=trv.tracking_id  and off.id=trk.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
        and trv.payment_status='pending' and trv.requested_status='declined'  group by trv.requested_date asc 
        union all
        select mlc.requested_date as requested date, mlc.requested_status as requested_status
        from  tbl_trackvalue as trv ,tbl_tracking as trk , tbl_offers as off , tblusers as usr, tbl_mailchimp_trackvalue as mlc
        where trv.tracking_id=mlc.tracking_id  and off.id=trk.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
        and mlc.payment_status='pending' and mlc.requested_status='declined'  group by mlc.requested_date asc ) t 
group by requested_date

UDPATE II: I deleted the aliases from the selections and that seems to have fixed the syntax error. I'll do more testing to make sure it works for a variety of cases. Thanks for the help. I didn't know you can't use aliases for your selects.

Upvotes: 1

Views: 1397

Answers (2)

Rob Farley
Rob Farley

Reputation: 15849

If the rows are exactly the same except for tracking_id, just remove that from your list and use UNION instead of UNION ALL. If you have other differences, you may prefer to use GROUP BY on your final results, so that you can use aggregate functions on other values that are different.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Remove tracking_id from the subqueries and use union:

(select trv.requested_date, trv.requested_status
from  tbl_trackvalue as trv, tbl_tracking as t , tbl_offers as off , tblusers as usr
where t.id=trv.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and trv.payment_status='pending' and trv.requested_status='declined'  group by trv.tr_user_id, trv.requested_date order by trv.requested_date asc )
union
(select mlc.requested_date, mlc.requested_status
from  tbl_trackvalue as trv ,tbl_tracking as t , tbl_offers as off , tblusers as usr, tbl_mailchimp_trackvalue as mlc
where trv.tracking_id=mlc.tracking_id  and off.id=t.offer_id and  usr.id=trv.tr_user_id and usr.id='1454' 
and mlc.payment_status='pending' and mlc.requested_status='declined'  group by trv.tr_user_id, mlc.requested_date order by mlc.requested_date asc )

Upvotes: 1

Related Questions