MySQL Subqueries – Calculate Quantity of Tickets Based on Reserved Using Subqueries

countgroup byMySQLsubquery

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');

Best Answer

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.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Mysql – Getting a Deadlock,

The system is starting to complain about the number of comments.

I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.

The statement that appears to be causing the issue is this one.

INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')

Now, inventory items has a FOREIGN KEY constraint

CONSTRAINT FK_inventoryitems_1 FOREIGN KEY (characterid) REFERENCES characters (id) ON DELETE CASCADE

What is the result of the following query

SELECT COUNT(*) FROM characters WHERE characters.id = 595;

The values here are also of interest.

ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

[EDIT]

I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.

It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line

Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.

Also, check out this and the rest of the thread.

[EDIT] in response to "How do i check it? , can you guide me?"

What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.

Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)

[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".

See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as

INDEX `ii_type_ix` (type), 
INDEX `ii_account_id_ix` (accountid),

As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

Mysql – Getting a Deadlock,

Related Question