php - mysql join 3 tables related in pairs -


► context : work in museum (for real), people come everyday, buy tickets (humans) , buy tickets drinks , foods (objects). there events, tickets have same names per event prices different.

► problem : have create report 2 results : total sales (visitors + food + drinks) , how many people came (visitors only) specific event. next image of 3 tables in database, how relate , sample data :

  • table tickets relates sales_main through event_id column.
  • table sales_main relates sales_detail through idmain_id columns.
  • table sales_detail have column ticket_name it's not unique in table tickets.

enter image description here

► question : how both results, total sales , human count, event 555 in 1 "select" ? tried next 2 "select" when combine them inner join cartesian results :

get detail sales event 555 :

select sales_detail.* sales_main inner join sales_detail on sales_detail.main_id = sales_main.id sales_main.event_id = '555' 

get tickets event 555 :

select * tickets tickets.event_id = '555' 

use:

select    sum(case when sd.ticket_name in ('adult', 'child') sd.quantity             else 0 end) total_visitors,   sum(sd.quantity * t.price)  total_sales sales_main sm join sales_detail sd   on sd.main_id = sm.id join ticket t   on t.event_id = sm.event_id  , t.ticket_name = sd.ticket_name sm.event_id = '555'; 

conditional aggregation based on type:

sum(case when t.ticket_type ='human' sd.quantity else 0 end) 

Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -