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 < warth33@hotmail.com> wrote in
<1183622589.686394.3480@m36g2000hse.googlegroups.c om>:
> table actors:
> id, name
>
> table movies_actors:
> movie_id, actor_id
>
> table stats_movies:
> movie_id
>
> 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.
>
> 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?

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

> This is easy to do using joins and grouping:
>
> 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.
>
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 < warth33@hotmail.com> wrote in
<1183625851.777328.101350@k79g2000hse.googlegroups .com>:
>> 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)
>
> 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

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

On 5 Jul, 09:57, wart...@hotmail.com wrote:
> > This is easy to do using joins and grouping:
>
> > 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.
>
> 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 -

So are you saying that you want a query that says

Brad Pitt 30
John Wayne 22

and so on?

> So are you saying that you want a query that says
>
> Brad Pitt 30
> John Wayne 22
>
> and so on?- Dölj citerad text -
>
> - Visa citerad text -

Yes, thats right.

On 5 Jul, 10:35, wart...@hotmail.com wrote:
> > So are you saying that you want a query that says
>
> > Brad Pitt 30
> > John Wayne 22
>
> > and so on?- Dölj citerad text -
>
> > - Visa citerad text -
>
> Yes, thats right.

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

On 5 Jul, 10:09, Pavel Lepin <p.le...@ctncorp.com> wrote:
> wart...@hotmail.com < wart...@hotmail.com> wrote in
> <1183625851.777328.101...@k79g2000hse.googlegroups .com>:
>
>
>
>
>
> >> 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)
>
> > 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
>
> 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 -

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.