SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Location
    Poland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query question (group_concat)

    Hello.
    I've 2 tables:
    Code:
    movies
    movie_id | movie_name          | movie_leadactor | movie_director
    1        | Into the Wild       | 1               | 2
    2        | Requiem for a Dream | 4               | 3
    
    people
    people_id | people_name      | people_isactor | people_isdir
    1         | Emile Hirsch     | 1              | 0
    2         | Sean Penn        | 0              | 1
    3         | Darren Aronofsky | 0              | 1
    4         | Ellen Burstyn    | 1              | 0
    with query:
    Code:
    SELECT GROUP_CONCAT(p.people_name) AS people, m.movie_name
    FROM people AS p
    JOIN movies AS m
    ON m.movie_leadactor = p.people_id 
    OR m.movie_director = p.people_id 
    GROUP BY m.movie_name
    I get
    Code:
    people 	| movie_name
    Emile Hirsch,Sean Penn  | 	Into the Wild
    Darren Aronofsky,Ellen Burstyn 	| Requiem for a Dream
    How to query to get always 'Actor' (its people_isactor) on first place and then on second place 'Director '(its people_isdir), because now for movie "Requiem.." 'Director' is on first place ?

  2. #2
    SitePoint Evangelist hexburner's Avatar
    Join Date
    Jan 2007
    Location
    Belgium
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try adding ORDER BY people.people_isactor DESC to your query.
    FOR SALE: 1 set of morals, never used, will sell cheap

  3. #3
    SitePoint Member
    Join Date
    Jul 2009
    Location
    Poland
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed query to
    Code:
    SELECT GROUP_CONCAT(p.people_name) AS people, m.movie_name
    FROM movies AS m
    JOIN (SELECT * 
            FROM people 
            ORDER BY people_isactor ASC) AS p
    ON m.movie_leadactor = p.people_id 
    OR m.movie_director = p.people_id 
    GROUP BY m.movie_name
    and now works. Thanks for suggestion.


Bookmarks

Posting Permissions

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