How to write mysql query, where i need to put order by before group by-CodePudding

//I know that there are questions like this before, but I can't get this to work

So, I have 3 tables, seats(id, type_id, hall_id), seat_types(id(PK), type_name) and taken_seats(screening_id(PK), seat_id(PK), type_id). I need to write a query where I pass a screening ID and hall ID, and get back seat_id,type_name, and isTaken (if seat is taken or not). The problem I have is that I have a query that return a duplicate if I do ORDER BY, but if I do GROUP BY I don't get correct data back because I didn't ordered it.

My query:

USE cinema_app;
SELECT 
    seats.id,
    seat_types.type_name,
    taken_seats.screening_id,
    CASE
    WHEN taken_seats.screening_id = 3 THEN 'ATaken'
        ELSE 'Not taken'
    END AS isTaken
FROM seats
JOIN seat_types ON seats.type_id = seat_types.id
LEFT JOIN taken_seats ON taken_seats.seat_id = seats.id
WHERE seats.hall_id = 2
ORDER BY seats.id, isTaken;

Table I'm getting back: https://imgur.com/tnmCIHi (id 181)

Database create script:

DROP DATABASE cinema_app;

CREATE DATABASE cinema_app CHARACTER SET UTF8MB4; USE cinema_app;

CREATE TABLE roles ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(20) NOT NULL );

CREATE TABLE ranks ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, rank_name VARCHAR(20) NOT NULL );

CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, birthdate DATE NOT NULL, email VARCHAR(100) NOT NULL, pass VARCHAR(20) NOT NULL, phone_number VARCHAR(20) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW(), role_id INT NOT NULL, FOREIGN KEY (role_id) REFERENCES roles(id) );

CREATE TABLE user_ranks ( user_id INT NOT NULL, rank_id INT NOT NULL, PRIMARY KEY (user_id, rank_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (rank_id) REFERENCES ranks(id) );

CREATE TABLE movies ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, TMDB_id INT NOT NULL );

CREATE TABLE spectacles ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, poster_img MEDIUMBLOB, time_length TIME NOT NULL );

CREATE TABLE hall_types ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, type_name VARCHAR(10) NOT NULL );

CREATE TABLE halls ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, type_id INT NOT NULL, FOREIGN KEY (type_id) REFERENCES hall_types(id) );

CREATE TABLE screening_types ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, type_name VARCHAR(20) NOT NULL );

CREATE TABLE screening_visions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, vision_name VARCHAR(10) NOT NULL );

CREATE TABLE screenings ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, starting_date_time TIMESTAMP NOT NULL, ending_date_time TIMESTAMP NOT NULL, first_pause BOOL NOT NULL, second_pause BOOL NOT NULL, ticket_price DECIMAL(4,2) NOT NULL, private_screening BOOL NOT NULL, hall_id INT NOT NULL, movie_id INT, spectacle_id INT, type_id INT NOT NULL, vision_id INT NOT NULL, FOREIGN KEY (hall_id) REFERENCES halls(id), FOREIGN KEY (movie_id) REFERENCES movies(id), FOREIGN KEY (spectacle_id) REFERENCES spectacles(id), FOREIGN KEY (type_id) REFERENCES screening_types(id), FOREIGN KEY (vision_id) REFERENCES screening_visions(id) );

CREATE TABLE private_screening_reqs ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, starting_date_time TIMESTAMP NOT NULL, ending_date_time TIMESTAMP NOT NULL, first_pause BOOL NOT NULL, second_pause BOOL NOT NULL, people_number INT NOT NULL, price DECIMAL(5,2) NOT NULL, TMDB_id INT NOT NULL, hall_id INT NOT NULL, user_id INT NOT NULL, FOREIGN KEY (hall_id) REFERENCES halls(id), FOREIGN KEY (user_id) REFERENCES users(id) );

CREATE TABLE seat_types ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, type_name VARCHAR(15) NOT NULL );

CREATE TABLE seats ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, type_id INT NOT NULL, hall_id INT NOT NULL, FOREIGN KEY (hall_id) REFERENCES halls(id), FOREIGN KEY (type_id) REFERENCES seat_types(id) );

CREATE TABLE taken_seats ( screening_id INT NOT NULL, seat_id INT NOT NULL, user_id INT, PRIMARY KEY (screening_id, seat_id), FOREIGN KEY (screening_id) REFERENCES screenings(id), FOREIGN KEY (seat_id) REFERENCES seats(id), FOREIGN KEY (user_id) REFERENCES users(id) );

SAMPLE data (CSV):

hall_types:

id,type_name 1,2D 2,"2D Extreme" 3,3D 4,"3D Extreme" 5,"Real 3D" 6,4DX

halls:

id,type_id 1,1 2,2 3,2 4,5 5,6

screening_types:

id,type_name 1,Normal 2,Premiere 3,Preprempere 4,"Late night" 5,Kids

screening_visions:

id,vision_name 1,2D 2,"2D Extreme" 3,3D 4,"3D Extreme" 5,"Real 3D" 6,4DX

movies:

id,TMDB_id 1,255 2,314 3,142

screenings:

id,starting_date_time,ending_date_time,first_pause,second_pause,ticket_price,private_screening,hall_id,movie_id,spectacle_id,type_id,vision_id 1,"2022-03-24 19:00:00","2022-03-24 20:00:00",0,0,8.00,0,2,1,NULL,1,2 2,"2022-03-24 21:30:00","2022-03-24 22:45:00",1,0,7.50,0,1,1,NULL,1,1 3,"2022-03-24 20:10:00","2022-03-24 22:10:00",1,0,9.00,0,2,2,NULL,2,2 4,"2022-03-25 20:10:00","2022-03-25 22:10:00",0,0,7.00,0,3,3,NULL,2,3

seat_types:

id,type_name 1,Normal 2,VIP 3,LoveBox

seats:

id,type_id,hall_id 1,1,1 2,1,1 3,1,1 4,1,1 5,1,1 6,1,1 7,1,1 8,1,1 9,1,1 10,1,1 11,1,1 12,1,1 13,1,1 14,1,1 15,1,1 16,1,1 17,1,1 18,1,1 19,1,1 20,1,1 21,1,1 22,1,1 23,1,1 24,1,1 25,1,1 26,1,1 27,1,1 28,1,1 29,1,1 30,1,1 31,1,1 32,1,1 33,1,1 34,1,1 35,1,1 36,1,1 37,1,1 38,1,1 39,1,1 40,1,1 41,1,1 42,1,1 43,1,1 44,1,1 45,1,1 46,1,1 47,1,1 48,1,1 49,1,1 50,1,1 51,1,1 52,1,1 53,1,1 54,1,1 55,1,1 56,1,1 57,1,1 58,1,1 59,1,1 60,1,1 61,1,1 62,1,1 63,1,1 64,1,1 65,1,1 66,1,1 67,1,1 68,1,1 69,1,1 70,1,1 71,1,1 72,1,1 73,1,1 74,1,1 75,1,1 76,1,1 77,1,1 78,1,1 79,1,1 80,1,1 81,1,1 82,1,1 83,1,1 84,1,1 85,1,1 86,1,1 87,1,1 88,1,1 89,1,1 90,1,1 91,1,1 92,1,1 93,1,1 94,1,1 95,1,1 96,1,1 97,1,1 98,1,1 99,1,1 100,1,1 101,1,1 102,1,1 103,1,1 104,1,1 105,1,1 106,1,1 107,1,1 108,1,1 109,1,1 110,1,1 111,1,1 112,1,1 113,1,1 114,1,1 115,1,1 116,1,1 117,1,1 118,1,1 119,1,1 120,2,1 121,2,1 122,2,1 123,2,1 124,2,1 125,2,1 126,2,1 127,2,1 128,2,1 129,2,1 130,2,1 131,2,1 132,2,1 133,2,1 134,2,1 135,2,1 136,2,1 137,3,1 138,3,1 139,3,1 140,3,1 141,3,1 142,3,1 143,3,1 144,3,1 145,3,1 146,1,2 147,1,2 148,1,2 149,1,2 150,1,2 151,1,2 152,1,2 153,1,2 154,1,2 155,1,2 156,1,2 157,1,2 158,1,2 159,1,2 160,1,2 161,1,2 162,1,2 163,1,2 164,1,2 165,1,2 166,1,2 167,1,2 168,1,2 169,1,2 170,1,2 171,1,2 172,1,2 173,1,2 174,1,2 175,1,2 176,1,2 177,1,2 178,1,2 179,1,2 180,1,2 181,1,2 182,1,2 183,1,2 184,1,2 185,1,2 186,1,2 187,1,2 188,1,2 189,1,2 190,1,2 191,1,2 192,1,2 193,1,2 194,1,2 195,1,2 196,1,2 197,1,2 198,1,2 199,1,2 200,1,2 201,1,2 202,1,2 203,1,2 204,1,2 205,1,2 206,1,2 207,1,2 208,1,2 209,1,2 210,1,2 211,1,2 212,1,2 213,1,2 214,1,2 215,1,2 216,1,2 217,1,2 218,1,2 219,1,2 220,1,2 221,1,2 222,1,2 223,1,2 224,1,2 225,1,2 226,1,2 227,1,2 228,1,2 229,1,2 230,1,2 231,1,2 232,1,2 233,1,2 234,1,2 235,1,2 236,1,2 237,1,2 238,1,2 239,1,2 240,1,2 241,1,2 242,1,2 243,1,2 244,1,2 245,1,2 246,1,2 247,1,2 248,1,2 249,1,2 250,1,2 251,1,2 252,1,2 253,1,2 254,1,2 255,1,2 256,1,2 257,1,2 258,1,2 259,1,2 260,1,2 261,1,2 262,1,2 263,1,2 264,1,2 265,1,2 266,1,2 267,1,2 268,1,2 269,1,2 270,1,2 271,1,2 272,1,2 273,1,2 274,1,2 275,1,2 276,1,2 277,1,2 278,1,2 279,1,2 280,1,2 281,1,2 282,1,2 283,1,2 284,1,2 285,1,2 286,1,2 287,1,2 288,1,2 289,1,2 290,1,2 291,1,2 292,1,2 293,1,2 294,1,2 295,1,2 296,1,2 297,1,2 298,1,2 299,1,2 300,1,2 301,1,2 302,1,2 303,1,2 304,1,2 305,1,2 306,1,2 307,1,2 308,1,2 309,1,2 310,1,2 311,1,2 312,1,2 313,1,2 314,1,2 315,1,2 316,1,2 317,1,2 318,1,2 319,1,2 320,1,2 321,1,2 322,1,2 323,1,2 324,1,2 325,1,2 326,1,2 327,1,2 328,1,2 329,1,2 330,1,2 331,1,2 332,1,2 333,1,2 334,1,2 335,1,2 336,1,2 337,1,2 338,1,2 339,1,2 340,1,2 341,1,2 342,1,2 343,1,2 344,1,2 345,1,2 346,2,2 347,2,2 348,2,2 349,2,2 350,2,2 351,2,2 352,2,2 353,2,2 354,2,2 355,2,2 356,2,2 357,2,2 358,2,2 359,2,2 360,2,2 361,2,2 362,2,2 363,2,2 364,2,2 365,2,2 366,3,2 367,3,2 368,3,2 369,3,2 370,3,2 371,3,2 372,3,2 373,3,2 374,3,2 375,3,2 376,3,2 377,1,3 378,1,3 379,1,3 380,1,3 381,1,3 382,1,3 383,1,3 384,1,3 385,1,3 386,1,3 387,1,3 388,1,3 389,1,3 390,1,3 391,1,3 392,1,3 393,1,3 394,1,3 395,1,3 396,1,3 397,1,3 398,1,3 399,1,3 400,1,3 401,1,3 402,1,3 403,1,3 404,1,3 405,1,3 406,1,3 407,1,3 408,1,3 409,1,3 410,1,3 411,1,3 412,1,3 413,1,3 414,1,3 415,1,3 416,1,3 417,1,3 418,1,3 419,1,3 420,1,3 421,1,3 422,1,3 423,1,3 424,1,3 425,1,3 426,1,3 427,1,3 428,1,3 429,1,3 430,1,3 431,1,3 432,1,3 433,1,3 434,1,3 435,1,3 436,1,3 437,1,3 438,1,3 439,1,3 440,1,3 441,1,3 442,1,3 443,1,3 444,1,3 445,1,3 446,1,3 447,1,3 448,1,3 449,1,3 450,1,3 451,1,3 452,1,3 453,1,3 454,1,3 455,1,3 456,1,3 457,1,3 458,1,3 459,1,3 460,1,3 461,2,3 462,2,3 463,2,3 464,2,3 465,2,3 466,2,3 467,2,3 468,2,3 469,2,3 470,2,3 471,2,3 472,2,3 473,3,3 474,3,3 475,3,3 476,3,3 477,3,3 478,3,3 479,1,4 480,1,4 481,1,4 482,1,4 483,1,4 484,1,4 485,1,4 486,1,4 487,1,4 488,1,4 489,1,4 490,1,4 491,1,4 492,1,4 493,1,4 494,1,4 495,1,4 496,1,4 497,1,4 498,1,4 499,1,4 500,1,4 501,1,4 502,1,4 503,1,4 504,1,4 505,1,4 506,1,4 507,1,4 508,1,4 509,1,4 510,1,4 511,1,4 512,1,4 513,1,4 514,1,4 515,1,4 516,1,4 517,1,4 518,1,4 519,1,4 520,1,4 521,1,4 522,1,4 523,1,4 524,1,4 525,1,4 526,1,4 527,1,4 528,1,4 529,1,4 530,1,4 531,1,4 532,1,4 533,1,4 534,1,4 535,1,4 536,1,4 537,1,4 538,1,4 539,1,4 540,1,4 541,1,4 542,1,4 543,1,4 544,1,4 545,1,4 546,1,4 547,1,4 548,1,4 549,1,4 550,1,4 551,1,4 552,1,4 553,1,4 554,1,4 555,1,4 556,1,4 557,1,4 558,1,4 559,1,4 560,1,4 561,1,4 562,1,4 563,1,4 564,1,4 565,1,4 566,1,4 567,1,4 568,1,4 569,1,4 570,1,4 571,1,4 572,1,4 573,1,4 574,1,4 575,1,4 576,1,4 577,1,4 578,1,4 579,1,4 580,1,4 581,1,4 582,1,4 583,1,4 584,1,4 585,1,4 586,1,4 587,1,4 588,1,4 589,1,4 590,1,4 591,1,4 592,1,4 593,1,4 594,1,4 595,1,4 596,1,4 597,1,4 598,1,4 599,1,4 600,1,4 601,1,4 602,1,4 603,1,4 604,1,4 605,1,4 606,1,4 607,2,4 608,2,4 609,2,4 610,2,4 611,2,4 612,2,4 613,2,4 614,2,4 615,2,4 616,2,4 617,2,4 618,2,4 619,2,4 620,2,4 621,2,4 622,2,4 623,3,4 624,3,4 625,3,4 626,3,4 627,3,4 628,3,4 629,3,4 630,3,4 631,1,5 632,1,5 633,1,5 634,1,5 635,1,5 636,1,5 637,1,5 638,1,5 639,1,5 640,1,5 641,1,5 642,1,5 643,1,5 644,1,5 645,1,5 646,1,5 647,1,5 648,1,5 649,1,5 650,1,5 651,1,5 652,1,5 653,1,5 654,1,5 655,1,5 656,1,5 657,1,5 658,1,5 659,1,5 660,1,5 661,1,5 662,1,5 663,1,5 664,1,5 665,1,5 666,1,5 667,1,5 668,1,5 669,1,5 670,1,5 671,1,5 672,1,5 673,1,5 674,1,5 675,1,5 676,1,5 677,1,5 678,1,5 679,1,5 680,1,5 681,1,5 682,1,5 683,1,5 684,1,5 685,1,5 686,1,5 687,1,5 688,1,5 689,1,5 690,1,5 691,1,5 692,1,5 693,1,5 694,1,5 695,1,5 696,1,5 697,2,5 698,2,5 699,2,5 700,2,5 701,2,5 702,2,5 703,2,5 704,2,5 705,2,5 706,2,5 707,2,5 708,3,5 709,3,5 710,3,5 711,3,5 712,3,5

taken_seats:

screening_id,seat_id,user_id 1,176,NULL 1,181,NULL 2,1,NULL 2,4,NULL 2,5,NULL 3,178,NULL 3,179,NULL 3,180,NULL 3,181,NULL 3,200,NULL

I tried to add GROUP BY and it didn't work because i didn't get ordered first. Also I tried writing a subquery but I didn't succeed in that.

CodePudding user response：

Why do you join all taken seats for all screenings when you are only interested in screening 3? Make sure you join the taken seats for the screening in question.

SET @hall_id = 2;
SET @screening_id = 3;

SELECT
  s.id AS seat_id,
  st.type_name AS seat_type,
  @screening_id AS screening_id,
  CASE WHEN ts.screening_id IS NOT NULL 
    THEN 'Taken'
    ELSE 'Not taken'
  END AS is_taken
FROM seats s
JOIN seat_types st ON seats.type_id = seat_types.id
LEFT JOIN taken_seats ts ON ts.seat_id = s.id AND ts.screening_id = @screening_id
WHERE s.hall_id = @hall_id
ORDER BY s.id;

CodePudding user response：

You could try joining the table as sql expressions SELECT * FROM table1 LEFT JOIN ( SELECT * FROM another ORDER BY something ) as table2 ON table1.id = table2.id GROUP BY something

Not sure if that structure is correct but that's what I would do.