SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE inventory_player ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, item_id int(11) NOT NULL, slot_id int(11) NOT NULL, deleted_at_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB, AUTO_INCREMENT = 17, AVG_ROW_LENGTH = 1024, CHARACTER SET utf8mb4, COLLATE utf8mb4_general_ci; CREATE TABLE inventory_item_type_names ( id int(11) NOT NULL, name varchar(128) NOT NULL ) ENGINE = INNODB, AVG_ROW_LENGTH = 5461, CHARACTER SET utf8mb4, COLLATE utf8mb4_general_ci; CREATE TABLE inventory_items ( id int(11) NOT NULL AUTO_INCREMENT, item_id int(11) NOT NULL, model_id int(11) NOT NULL, item_type int(4) NOT NULL, x_rot double NOT NULL, y_rot double NOT NULL, z_rot double NOT NULL, zoom double NOT NULL, weight double NOT NULL, name varchar(128) DEFAULT NULL, descr varchar(128) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB, AUTO_INCREMENT = 6, AVG_ROW_LENGTH = 8192, CHARACTER SET utf8mb4, COLLATE utf8mb4_general_ci; INSERT INTO inventory_player VALUES (1, 4, 0, 0, NULL), (2, 4, 0, 1, NULL), (3, 4, 0, 2, NULL), (4, 4, 0, 3, NULL), (5, 4, 1, 4, NULL), (6, 4, 1, 4, NULL), (7, 4, 1, 4, NULL), (8, 4, 1, 4, NULL), (9, 4, 0, 5, NULL), (10, 4, 0, 6, NULL), (11, 4, 0, 7, NULL), (12, 4, 0, 8, NULL), (13, 4, 1, 4, NULL), (14, 4, 1, 4, NULL), (15, 4, 1, 4, NULL), (16, 4, 1, 4, NULL); INSERT INTO inventory_item_type_names VALUES (1, 'Accessories'), (2, 'Skins'), (3, 'Phone'); INSERT INTO inventory_items VALUES (1, 0, 18874, 3, 90, 180, 0, 1, 2, 'name1', 'descr1'), (4, 1, 321, 1, 0, 90, 0, 1.5, 0.2, 'name2', 'descr2'); SELECT inv_plr.id AS user_db_item_id, inv_plr.slot_id, inv_plr.user_id, ii_type_names.name AS item_type_name, inv_items.*, COUNT(inv_items.name) AS count_item, SUM(inv_items.weight) AS sum_weight FROM inventory_player inv_plr INNER JOIN inventory_items inv_items ON inv_items.item_id = inv_plr.item_id INNER JOIN inventory_item_type_names ii_type_names ON ii_type_names.ID = inv_items.item_type WHERE inv_plr.user_id = 4 GROUP BY inv_plr.user_id, CASE WHEN item_type IN (1) THEN inv_items.name ELSE inv_plr.id END ORDER BY user_db_item_id