Professional Web Applications Themes

difficult query? - MySQL

probably its not so difficult for experienced mysql guys. But it is for me. Please, let me explain. These are the relevant fields in the involved tables: table actors: id, name table movies_actors: movie_id, actor_id table stats_movies: movie_id Brad Pitt is id = 1. he has done a lot of movies. So in movies_actors there are a lot of records with him: movie_id = 1, actor_id = 1 movie_id = 2, actor_id = 1 movie_id = 3, actor_id = 1 In the table stats_movies there are those movies that have been bought. Lets say that movie_ids 1 and 2 have ...

  1. #1

    Default difficult query?

    probably its not so difficult for experienced mysql guys. But it is
    for me. Please, let me explain.
    These are the relevant fields in the involved tables:

    table actors:
    id, name

    table movies_actors:
    movie_id, actor_id

    table stats_movies:
    movie_id

    Brad Pitt is id = 1. he has done a lot of movies. So in movies_actors
    there are a lot of records with him:
    movie_id = 1, actor_id = 1
    movie_id = 2, actor_id = 1
    movie_id = 3, actor_id = 1

    In the table stats_movies there are those movies that have been
    bought. Lets say that movie_ids 1 and 2 have been bought. And these
    movies can be bought many times...Now, I need to know how many movies
    where Brad Pitt played, has been sold. how can I do this?
    I need to sum the the actor_id in movies_actors (grouping by actor_id
    I guess? ) where the movie_id is in the table stats_movies.

    The problem is that in stats_movies there can be a lot od movie_id =
    1. Brad pitt played movie_id = 1, and movie_id = 1 has been sold 10
    times. Brad pitt also played in movie_id = 2, and that movie was sold
    3 times.

    So I need to create a queery that in the example above would return
    Brad Pitt has sold 13 movies. How can I do that?

    Many thanks.

    warth33@hotmail.com Guest

  2. #2

    Default Re: difficult query?


    com < com> wrote in
    <googlegroups.com>: 

    This is easy to do using joins and grouping:

    mysql> SELECT * FROM actors;
    +----+-------------+
    | id | name |
    +----+-------------+
    | 1 | Brad Pitt |
    | 2 | Fred Foonly |
    +----+-------------+
    2 rows in set (0.00 sec)

    mysql> SELECT * FROM movies_actors;
    +----------+----------+
    | movie_id | actor_id |
    +----------+----------+
    | 1 | 1 |
    | 2 | 1 |
    | 3 | 1 |
    | 2 | 2 |
    | 3 | 2 |
    | 4 | 2 |
    +----------+----------+
    6 rows in set (0.00 sec)

    mysql> SELECT * FROM stats_movies;
    +----------+
    | movie_id |
    +----------+
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 1 |
    | 2 |
    | 2 |
    | 2 |
    | 4 |
    +----------+
    14 rows in set (0.00 sec)

    mysql> SELECT COUNT(stats_movies.movie_id) FROM actors JOIN
    movies_actors ON (actors.id=movies_actors.actor_id) JOIN
    stats_movies USING (movie_id) WHERE actors.name='Brad Pitt'
    GROUP BY actors.id;
    +------------------------------+
    | COUNT(stats_movies.movie_id) |
    +------------------------------+
    | 13 |
    +------------------------------+
    1 row in set (0.00 sec)

    mysql> SELECT COUNT(stats_movies.movie_id) FROM actors JOIN
    movies_actors ON (actors.id=movies_actors.actor_id) JOIN
    stats_movies USING (movie_id) WHERE actors.name='Fred
    Foonly' GROUP BY actors.id;
    +------------------------------+
    | COUNT(stats_movies.movie_id) |
    +------------------------------+
    | 4 |
    +------------------------------+
    1 row in set (0.00 sec)

    You might want to read about joins, grouping and aggregate
    functions in MySQL Reference Manual. If that's too hard to
    digest, some sort of introductory reading/tutorial should
    give a good overview of those.

    --
    Pavel Lepin
    Pavel Guest

  3. #3

    Default Re: difficult query?

    > This is easy to do using joins and grouping: 
    Thank you for your time Pavel!
    Yes, Its probably a little bit hard to digest.

    However, what I meant with my post was that i needed a query that
    would also fetch names from the table actors. So there cannot be Brad
    pitt in the where clause.

    I tested a query, maybe not so good designed, but it seems to work.

    SELECT A.the_name, count( SM.movie_id ) as tot
    FROM actors AS A, movies_actors AS MA, stats_movies AS SM
    WHERE A.id = MA.actor_id
    AND MA.movie_id = SM.movie_id
    GROUP BY A.the_name
    ORDER BY tot DESC

    Does it make sense?

    warth33@hotmail.com Guest

  4. #4

    Default Re: difficult query?


    com < com> wrote in
    <googlegroups.com>: 
    >
    > However, what I meant with my post was that i needed a
    > query that would also fetch names from the table actors.
    > So there cannot be Brad pitt in the where clause.
    >
    > SELECT A.the_name, count( SM.movie_id ) as tot
    > FROM actors AS A, movies_actors AS MA, stats_movies AS SM
    > WHERE A.id = MA.actor_id
    > AND MA.movie_id = SM.movie_id
    > GROUP BY A.the_name
    > ORDER BY tot DESC[/ref]

    Makes perfect sense. Our DBA says explicit JOIN syntax may
    lead to better performance than using the WHERE clause for
    join conditions, but since I myself am not a MySQL query
    optimisation expert, don't take my word on it.

    --
    Pavel Lepin
    Pavel Guest

  5. #5

    Default Re: difficult query?

    On 5 Jul, 09:57, com wrote: 



    >
    > Thank you for your time Pavel!
    > Yes, Its probably a little bit hard to digest.
    >
    > However, what I meant with my post was that i needed a query that
    > would also fetch names from the table actors. So there cannot be Brad
    > pitt in the where clause.
    >
    > I tested a query, maybe not so good designed, but it seems to work.
    >
    > SELECT A.the_name, count( SM.movie_id ) as tot
    > FROM actors AS A, movies_actors AS MA, stats_movies AS SM
    > WHERE A.id = MA.actor_id
    > AND MA.movie_id = SM.movie_id
    > GROUP BY A.the_name
    > ORDER BY tot DESC
    >
    > Does it make sense?- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    So are you saying that you want a query that says

    Brad Pitt 30
    John Wayne 22

    and so on?

    Captain Guest

  6. #6

    Default Re: difficult query?

     

    Yes, thats right.

    warth33@hotmail.com Guest

  7. #7

    Default Re: difficult query?

    On 5 Jul, 10:35, com wrote: 



    >
    > Yes, thats right.[/ref]

    OK we can do that. But that's not what you asked for originally and so
    that's not what Pavel gave you.

    Captain Guest

  8. #8

    Default Re: difficult query?

    On 5 Jul, 10:09, Pavel Lepin <com> wrote: [/ref]


    >
    > Makes perfect sense. Our DBA says explicit JOIN syntax may
    > lead to better performance than using the WHERE clause for
    > join conditions, but since I myself am not a MySQL query
    > optimisation expert, don't take my word on it.
    >
    > --
    > Pavel Lepin- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Depends on the optimiser. In testing with MySQL I have found that it
    manages to figure these things out. However teh explicit joins are
    easier to maintain.

    Captain Guest

Similar Threads

  1. Difficult query
    By eebieeebie@hotmail.com in forum MySQL
    Replies: 4
    Last Post: April 19th, 11:18 AM
  2. Using MS Access, is it difficult to query twodatasources?
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: November 15th, 02:02 AM
  3. How difficult it is?
    By Paul Mitchum in forum Mac Programming
    Replies: 2
    Last Post: September 9th, 01:42 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139