Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016
    Posts
    2
    Provided Answers: 1

    Answered: group_concat and limit

    Hi all,
    i use this query but it returns error code 1242: Subquery returns more than 1 row in workbench
    What I really need is to get limited the first 6 actors depending by the `ST_peoplefilms`.`cnt`

    ID_film ----- T_people.person
    111 actor1, actor2 .... actor 6

    SELECT
    `ST_peoplefilms`.`cnt` AS `cnt`,
    `ST_peoplefilms`.`ID_person` AS `ID_person`,
    `MT_films`.`ID_films` AS `ID_films`,
    `MT_films`.`Title1` AS `Title`,
    `MT_films`.`prilipsi` AS `prilipsi`,
    `ST_peoplefilms`.`ID_idiotita` AS `idiotita`,
    `T_idiotita`.`idiotita` AS `intiotita`,
    `MT_films`.`room_last` AS `room_last`,
    GROUP_CONCAT((SELECT
    `T_people`.`person`
    FROM
    `T_people`
    WHERE
    (((`ST_peoplefilms`.`ID_person` = `T_people`.`ID_person`)
    AND `ST_peoplefilms`.`cnt`) <= 6))
    ORDER BY `ST_peoplefilms`.`cnt` ASC
    SEPARATOR ', ') AS `person`
    FROM
    (`T_idiotita`
    JOIN (`T_people`
    JOIN (`MT_films`
    JOIN `ST_peoplefilms` ON ((`MT_films`.`ID_films` = `ST_peoplefilms`.`ID_films`))) ON ((`T_people`.`ID_person` = `ST_peoplefilms`.`ID_person`))) ON ((`T_idiotita`.`ID_idiotita` = `ST_peoplefilms`.`ID_idiotita`)))
    GROUP BY `ST_peoplefilms`.`cnt`
    HAVING (((`ST_peoplefilms`.`ID_idiotita` = 2) //the next lines all reference to actor/actress
    OR (`ST_peoplefilms`.`ID_idiotita` = 13)
    OR (`ST_peoplefilms`.`ID_idiotita` = 40)
    OR (`ST_peoplefilms`.`ID_idiotita` = 44)
    OR (`ST_peoplefilms`.`ID_idiotita` = 45))
    AND (`MT_films`.`room_last` = 3)) //the specific movie I need
    ORDER BY `ST_peoplefilms`.`cnt`

    another try was

    SELECT
    `MT_films`.`ID_films` AS `ID_films`,
    `ST_peoplefilms`.`ID_idiotita` AS `ID_idiotita`,
    `T_idiotita`.`idiotita` AS `idiotita`,
    `ST_peoplefilms`.`cnt` AS `cnt`,
    `MT_films`.`room_last` AS `room_last`,
    GROUP_CONCAT(DISTINCT `T_people`.`person`
    ORDER BY `ST_peoplefilms`.`cnt` ASC
    SEPARATOR ', ') AS `person`

    which works but it returns the total of the actors by ID_films something means even 150 rows, while it doesn't limit

    Could you please help?
    Thank's in advance
    /kostas

  2. Best Answer
    Posted by kost36

    "just solved...
    the solution was out of the subquery
    WHERE
    ((`ST_peoplefilms`.`cnt` < 7)

    SELECT
    `ST_peoplefilms`.`cnt` AS `cnt`,
    `MT_films`.`ID_films` AS `ID_films`,
    `MT_films`.`Title1` AS `Title1`,
    `MT_films`.`room_last` AS `room_last`,
    GROUP_CONCAT(DISTINCT `T_people`.`person`
    ORDER BY `ST_peoplefilms`.`cnt` ASC
    SEPARATOR ', ') AS `person`
    FROM
    (`MT_films`
    JOIN (`T_people`
    JOIN `ST_peoplefilms` ON ((`T_people`.`ID_person` = `ST_peoplefilms`.`ID_person`))) ON ((`MT_films`.`ID_films` = `ST_peoplefilms`.`ID_films`)))
    WHERE
    ((`ST_peoplefilms`.`cnt` < 7)
    AND (`ST_peoplefilms`.`cnt` IS NOT NULL)
    AND ((`ST_peoplefilms`.`ID_idiotita` = 2)
    OR (`ST_peoplefilms`.`ID_idiotita` = 13)
    OR (`ST_peoplefilms`.`ID_idiotita` = 40)
    OR (`ST_peoplefilms`.`ID_idiotita` = 44)
    OR (`ST_peoplefilms`.`ID_idiotita` = 45))
    AND (`MT_films`.`room_last` = 3))
    ORDER BY `ST_peoplefilms`.`cnt`

    thanks for your time
    /kostas"


  3. #2
    Join Date
    May 2016
    Posts
    2
    Provided Answers: 1

    group_concat and limit SOLVED!!!

    just solved...
    the solution was out of the subquery
    WHERE
    ((`ST_peoplefilms`.`cnt` < 7)

    SELECT
    `ST_peoplefilms`.`cnt` AS `cnt`,
    `MT_films`.`ID_films` AS `ID_films`,
    `MT_films`.`Title1` AS `Title1`,
    `MT_films`.`room_last` AS `room_last`,
    GROUP_CONCAT(DISTINCT `T_people`.`person`
    ORDER BY `ST_peoplefilms`.`cnt` ASC
    SEPARATOR ', ') AS `person`
    FROM
    (`MT_films`
    JOIN (`T_people`
    JOIN `ST_peoplefilms` ON ((`T_people`.`ID_person` = `ST_peoplefilms`.`ID_person`))) ON ((`MT_films`.`ID_films` = `ST_peoplefilms`.`ID_films`)))
    WHERE
    ((`ST_peoplefilms`.`cnt` < 7)
    AND (`ST_peoplefilms`.`cnt` IS NOT NULL)
    AND ((`ST_peoplefilms`.`ID_idiotita` = 2)
    OR (`ST_peoplefilms`.`ID_idiotita` = 13)
    OR (`ST_peoplefilms`.`ID_idiotita` = 40)
    OR (`ST_peoplefilms`.`ID_idiotita` = 44)
    OR (`ST_peoplefilms`.`ID_idiotita` = 45))
    AND (`MT_films`.`room_last` = 3))
    ORDER BY `ST_peoplefilms`.`cnt`

    thanks for your time
    /kostas

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •