2

I'm designing a database that will hold ticketing information. The 3 tables I use to track ticketing are

  • tbl_ticket_type to the types of events an event can have
  • tbl_ticket_count to keep track of how many tickets of a particular type should be sold for an event
  • tbl_tickets to keep track of the actual tickets purchased by users.

  • I'm trying to design a query that will calculate the number of ticket types left for a particular event and return the result in groups of the types and number left. I tried the following SQL statement but to no avail, how can I fix SQL to return types of tickets and quantities left? DB inset attached

    SELECT tt.ticket_type,COUNT(tt.ticket_type) 
    FROM tbl_tickets t 
    INNER JOIN tbl_ticket_count tc ON t.ticket_count_id=tc.ticket_count_id 
    INNER JOIN tbl_ticket_type tt 
    ON     tc.ticket_type_id = tt.ticket_type_id 
    WHERE ticket_count_id IN (SELECT tc.ticket_count_id FROM tbl_ticket_count WHERE event_id=15) 
    GROUP BY tt.ticket_type
    

    Script to recreate the three tables:

    tbl_tickets

    CREATE TABLE `tbl_tickets` (
      `ticket_id` int(11) NOT NULL ,
      `ticket_count_id` int(11) DEFAULT '0' ,
      `userid` int(11) DEFAULT '0' ,
      `provider_id` int(11) DEFAULT '1',
      `pay_status` enum('reserved','paid','pending','') DEFAULT 'reserved' ,
      `reference` varchar(50) DEFAULT NULL COMMENT 'if e-payment, what the reference for the payment',
      `event_status` enum('pending','checked','','') NOT NULL DEFAULT 'pending' ,
      `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
    
    ALTER TABLE `tbl_tickets`
      ADD PRIMARY KEY (`ticket_id`),
      ADD KEY `ticket_count_id` (`ticket_count_id`),
      ADD KEY `user_id` (`userid`),
      ADD KEY `provider_id` (`provider_id`);
    ALTER TABLE `tbl_tickets`
    ADD CONSTRAINT `tbl_tickets_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `tbl_provider` (`provider_id`),
    ADD CONSTRAINT `tbl_tickets_ibfk_2` FOREIGN KEY (`ticket_count_id`) REFERENCES `tbl_ticket_count` (`ticket_count_id`) ON DELETE CASCADE ON UPDATE CASCADE;
    INSERT INTO `tbl_tickets` (`ticket_id`, `ticket_count_id`, `userid`, `provider_id`, `pay_status`, `reference`, `event_status`, `date_time`) VALUES
    (3, 1, 2, 1, 'reserved', NULL, 'pending', '2017-11-04 15:14:15'),
    (4, 1, 6, 1, 'pending', NULL, 'pending', '2017-11-04 15:14:15'),
    (5, 10, 6, 1, 'reserved', NULL, 'pending', '2017-11-04 16:32:35'),
    (6, 10, 2, 1, 'reserved', NULL, 'pending', '2017-11-04 16:32:35');
    

    tbl_ticket_count

    CREATE TABLE `tbl_ticket_count` (
      `ticket_count_id` int(11) NOT NULL ,
      `ticket_type_id` int(11) DEFAULT '0' ,
      `event_id` int(11) DEFAULT '0' ,
      `quantity` int(11) DEFAULT '0' ,
      `price` decimal(10,0) DEFAULT '0' ,
      `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    ALTER TABLE `tbl_ticket_count`
      ADD PRIMARY KEY (`ticket_count_id`),
      ADD KEY `ticket_type_id` (`ticket_type_id`),
      ADD KEY `event_id` (`event_id`);
    ALTER TABLE `tbl_ticket_count`
    ADD CONSTRAINT `tbl_ticket_count_ibfk_1` FOREIGN KEY (`ticket_type_id`)     REFERENCES `tbl_ticket_type` (`ticket_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `tbl_ticket_count_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `tbl_events_new` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE;
    INSERT INTO `tbl_ticket_count` (`ticket_count_id`, `ticket_type_id`, `event_id`, `quantity`, `price`, `date_time`) VALUES
    (1, 1, 3, 10, 15000, '2017-11-04 14:45:02'),
    (2, 2, 3, 10, 25000, '2017-11-04 14:45:02'),
    (3, 1, 9, 10, 15000, '2017-11-04 14:45:39'),
    (4, 2, 9, 10, 25000, '2017-11-04 14:45:51'),
    (5, 1, 4, 100, 2000, '2017-11-04 14:47:13'),
    (6, 1, 5, 50, 4000, '2017-11-04 14:48:17'),
    (7, 5, 10, 15, 100000, '2017-11-04 14:48:17'),
    (8, 1, 8, 50, 0, '2017-11-04 14:48:48'),
    (9, 7, 15, 10, 3000, '2017-11-04 16:31:47'),
    (10, 1, 15, 20, 7500, '2017-11-04 16:31:47');
    

    tbl_ticket_type

    CREATE TABLE `tbl_ticket_type` (
      `ticket_type_id` int(11) NOT NULL COMMENT 'stores unique identification for the ticket type',
      `ticket_type` varchar(50) DEFAULT NULL COMMENT 'type of ticket ',
      `description` varchar(500) DEFAULT NULL COMMENT 'description of that ticket type_category',
      `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'datetime the type_category was added'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    ALTER TABLE `tbl_ticket_type`
      ADD PRIMARY KEY (`ticket_type_id`);
    INSERT INTO `tbl_ticket_type` (`ticket_type_id`, `ticket_type`, `description`, `date_time`) VALUES
    (1, 'Standard', 'These are standard entry tickets that will permit one person into an event venue', '2017-11-04 14:42:41'),
    (2, 'VIP', 'These are VIP tickets with VIP access to an event venue', '2017-11-04 14:42:41'),
    (3, 'VIPP', 'VIPP tickets to access the VVIP sections of an event venue', '2017-11-04 14:43:32'),
    (4, 'Family', 'Tickets to permit a family of a pre determined number by the organisers', '2017-11-04 14:43:32'),
    (5, 'Team', 'Team ticket to some events e.g. sporting tournaments', '2017-11-04 14:44:04'),
    (6, 'Couples', 'Admits two (2) people into an event venue', '2017-11-04 15:06:00'),
    (7, 'Children', 'Tickets aimed at children', '2017-11-04 16:31:00');
    
    • Images are a bad idea. Please delete the image and post the result of SHOW CREATE TABLE blah\G - help us to help you! – Vérace Nov 4 '17 at 20:15
    • removed the image and added sql statement instead, just having problems with the formatting @Vérace – Manny265 Nov 4 '17 at 21:59
    • Good - now we're getting somewhere! Can you also put in the FK relationships between the tables? Some sample data and your desired result would be really good! – Vérace Nov 4 '17 at 22:13
    • added the foreign key constraints and sample data @Vérace – Manny265 Nov 5 '17 at 8:00
    • 1
      You may have your answer below - could you in future take the time to actually test the code you post on a service like db-fiddle - or, better still, post the link here to an already working schema? It means a less cluttered post and it's more likely to be read/answered! Help us to help you! :-) – Vérace Nov 5 '17 at 10:47
    2

    Maybe the following queries will help you to find the correct answer (MySQL 5.7). Assumptions: we need the amount of sold and unsold tickets. Each row in the tbl_tickets represents ONE sold ticket. Using a LEFT JOIN, we include "unsold" tickets:

    select 
      tc.ticket_count_id  TC_ID
    , t.ticket_count_id   T_TC_ID
    , tc.ticket_type_id   CATEGORY
    , tc.event_id         EVENT
    , tc.quantity
    , count(t.ticket_count_id)  SOLD
    , tc.quantity - count(t.ticket_count_id) TICKETS_LEFT
    from tbl_ticket_count tc
      left join tbl_tickets t on tc.ticket_count_id = t.ticket_count_id 
    group by tc.ticket_count_id
    having tc.event_id = 15 ;
    
    -- result
    +-------+---------+----------+-------+----------+------+--------------+
    | TC_ID | T_TC_ID | CATEGORY | EVENT | quantity | SOLD | TICKETS_LEFT |
    +-------+---------+----------+-------+----------+------+--------------+
    |     9 |    NULL |        7 |    15 |       10 |    0 |           10 |
    |    10 |      10 |        1 |    15 |       20 |    2 |           18 |
    +-------+---------+----------+-------+----------+------+--------------+
    2 rows in set (0.00 sec)
    

    If that looks correct to you, join the third table, and adjust the columns of the SELECT clause.

    select 
      tc.event_id EVENT
    , tc.quantity - count(t.ticket_count_id) TICKETS_LEFT
    , tt.ticket_type
    from tbl_ticket_count tc
      left join tbl_tickets t on tc.ticket_count_id = t.ticket_count_id 
      join tbl_ticket_type tt on tc.ticket_type_id = tt.ticket_type_id
    group by tc.ticket_count_id
    having tc.event_id = 15 ;
    
    -- result
    +-------+--------------+-------------+
    | EVENT | TICKETS_LEFT | ticket_type |
    +-------+--------------+-------------+
    |    15 |           10 | Children    |
    |    15 |           18 | Standard    |
    +-------+--------------+-------------+
    2 rows in set (0.00 sec)
    

    Dbfiddle here.

    NOTE: The DDL code needed some minor changes for the test setup to work.
    E.g. tbl_tickets: duplicated value '' in ENUM. Some FKs have REFERENCES that fail e.g. tbl_tickets_ibfk_1.

    • wanted to add a condition where only tickets with a certain pay_status are chosen. where can I add that condition? @stefan – Manny265 Mar 28 '18 at 8:17
    • 1
      @Manny265: last query - change the GROUP BY and HAVING clauses to: group by tc.ticket_count_id, t.pay_status having tc.event_id = 15 and t.pay_status = 'reserved'; ( see dbfiddle @ dbfiddle.uk/… ) – stefan Mar 28 '18 at 17:28

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.