CODEWITHSUNDEEP
mysqlsql
Uncle
Uncle

Reputation: 71

SQL, avoiding to use inner select

I am making an event app, one that allows people to create events and ticket types together that have a name, quantity and price, when a user purchases a ticket the purchase is inserted in a different table, the purchase includes the event ID, ticket type ID and other fields. At this point I want to fetch all my ticket types with the ticket type name, quantity, price and available tickets.

Here is a graphic look of my tables

TicketTypeTable
---------------------------------
|id |eventfk | name | qty | price|
|--------------------------------|


TicketSalesTable
----------------------------------------------
|id |userfk |eventfk | ticketype | qty | price|
|---------------------------------------------|`

And the output I want is 

--------------------------------------------
|id |eventfk | name | qty |available | price|
|-------------------------------------------|

I can do this with an inner select like

select id
     , eventfk
     , name
     , qty (select count(*) from ticketsalestable) available
     , price 
  from tickettypetable

but this is bad for a large database...and it's just bad regardless of the situation.

Anyone know a better way out?

I also tried using a JOIN but am stuck

SELECT t.id, t.eventfk, t.name, t.quantity, t.price FROM tickettypetable t LEFT JOIN ticketsalestable s ON s.eventfk = t.eventfk WHERE t.eventfk = 10

Upvotes: 0

Views: 69

Answers (2)

Uncle
Uncle

Reputation: 71

Based on Turo's answer I modified it and came up with this

SELECT t.id, t.name, t.quantity, IF(t.price = 0,'FREE',t.price), (t.quantity - IFNULL(s.sold, 0)) as available FROM tickettypetb t LEFT JOIN (select sl.eventfk, sl.tickettypefk, COUNT(sl.eventfk) as sold from salestb sl GROUP BY sl.tickettypefk ORDER BY sl.tickettypefk DESC) s ON s.tickettypefk = t.id AND s.eventfk = t.eventfk WHERE t.eventfk = 22 GROUP BY t.id ORDER BY t.id DESC

Upvotes: 0

Turo
Turo

Reputation: 4914

I think you want a join with the aggregate of ticketsalestable, something like

SELECT t.id, t.eventfk, t.name, t.quantity, t.price, (t.qty - s.sold) as available FROM tickettypetable t LEFT JOIN 
    ( select eventfk, tickettype, sum(qty) as sold from ticketsalestable group by eventfk, tickettype) s
    ON s.eventfk = t.eventfk and t.id = s.tickettype
 WHERE t.eventfk = 10

Dependent on the optimizer it could be faster to add the where criteria also to the aggregate

SELECT t.id, t.eventfk, t.name, t.quantity, t.price, (t.qty - s.sold) as available FROM tickettypetable t LEFT JOIN 
    ( select eventfk, tickettype, sum(qty) as sold from ticketsalestable group by eventfk, tickettype where eventfk = 10) s
    ON s.eventfk = t.eventfk and t.id = s.tickettype
 WHERE t.eventfk = 10

EDIT ticketype is join-criteria, too

Upvotes: 1

Related Questions