yes, I saw the manual, but it was left lacking…
Thanks.
GROUP_CONCAT is awesome
good choice
This query you gave me produces repeated rows for the same movie, one row for each actor.
However, looking at the Sakila example DB I came up with this query:
select movies.id, movies.title, group_concat(concat(`actors`.`name`, ' ',`actors`.`lastName`) separator ', ') AS `actors`
from movies join `actor2title` on (`movies`.`id` = `actor2title`.`titleId`) join `actors` on (`actor2title`.`pId` = `actors`.`id`) group by `movies`.`id`;
which produces one row for each movie with a comma-separated list of the actors. Seems to work great. The group_concat function is new to me.
Of course, any other pointer would be greatly appreciated.
Hello again,
Now I need to do fulltext searches on this database. I’ve never used fulltext search before and have been reading up on it, but any pointers on how to create the indexes would be very welcome. I am wondering for example if it is possible to create one index that mixes columns from different tables. That sure would be handy.
Cheers.
SELECT movies.id
, movies.title
, actors.id
, actors.name
, actors.lastName
FROM movies
INNER
JOIN actor2title
ON actor2title.titleId = movies.id
INNER
JOIN actors
ON actors.id = actor2title.pId
ORDER
BY movies.title
, actors.lastName
Neither, I’m looking for a query that will list all movies with their respective actors. For example, Midnight Run with the 4 leading actors would produce 4 rows. I’m trying to find a way to have the movies with their actors so I can list them in a loop.
try this –
SELECT edition.id
, edition.title
, edition.rating
FROM actors
INNER
JOIN actor2title
ON actor2title.pid = actors.id
INNER
JOIN edition
ON edition. = actor2title.titleId
WHERE actors.name LIKE '%robert%'
OR actors.lastName LIKE '%robert'
to see how it actually executes, and how the execution resembles your statement closely, run an EXPLAIN on this query, then compare it to the EXPLAIN for your sub-subquery approach
I came up with this query that uses 2 subqueries but of course it’s terribly inneficient:
select id from edition where id in
(select titleId from actor2title where pId in
(select id from actors where name like '%robert%' or lastName like '%robert') )
Hello Rudy and all the other SQL gods.
I have a followup question.
I want to search by actor, but then also have the title id in the dvdpedia table that contains entries for those actors.
This is easy, but I want to do it in a way that initially only searches the actors table and then searches the dvdpedia table only if there is a match found in the actors table so as to have a much faster query. But I’m not sure how to do that other than 2 separate queries, the second one with the ID’s from the first one, though I’m thinking there has to be a better way no?
that’s a pretty big difference, wouldn’t you say?
can you post both EXPLAINs?
stop trying to outthink the optimizer – it’s ~way~ smarter than you or me
I think a junction table exactly as stated will do the trick.
Chris
Thanks Rudy, can’t really determine that much difference between them with EXPLAIN, other than the fact that my query has 3 lookups for a WHERE clause and yours has one, so I think that makes it ‘lighter’.
What I’m thinking is that it might be better to run the query on the actors table by itself and then only run another query if a match was found, but that might take a littl emore time. Not sure.
i gave you two queries, which one are you talking about?
That’s beautiful!
But is there a way to get the movie titles and movie info along with all the actors’s name with just one query? This is the part that I am missing.
guelphdad, I have not seen this database, will take a look, thanks.
list all the movies for a specific actor –
SELECT movies.id
, movies.title
FROM actor2title
INNER
JOIN movies
ON movies.id = actor2title.titleId
WHERE actor2title.pId = 937
list all the actors for a specific movie –
SELECT actors.id
, actors.name
, actors.lastName
FROM actor2title
INNER
JOIN actors
ON actors.id = actor2title.pId
WHERE actor2title.titleId = 21
Have you looked at the Sakila practice database on the mysql site? It is a movie database. It might give you some ideas as well.
of course now a single select will produce a cartesian product of repeated values for a movie for each actor involved in it when using a junction table.
Sorry, I’m a bit rusty on many to many relationships, how can I avoid this?
no it won’t
if you could do a SHOW CREATE TABLE for each of your tables, i’ll show you the query
As usual, you come to my rescue, here are the create table commands, I have a couple more lookup tables, but I’m only listing 2 so as not to try your patience
CREATE TABLE `actor2title` (
`pId` int(11) NOT NULL DEFAULT '0',
`titleId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`pId`,`titleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `actors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`lastName` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `movies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`studioId` int(11) DEFAULT NULL,
`genreId` int(11) DEFAULT NULL,
`ratedId` int(11) DEFAULT NULL,
`duration` timestamp NULL DEFAULT NULL,
`theatrical` date DEFAULT NULL,
`release` date DEFAULT NULL,
`rating` int(20) DEFAULT NULL,
`price` decimal(4,2) DEFAULT NULL,
`soundId` int(11) DEFAULT NULL,
`countryId` int(11) DEFAULT NULL,
`awards` text,
`comments` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE `writer2dvd` (
`pId` int(11) NOT NULL DEFAULT '0',
`titleId` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`pId`,`titleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `writers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`lastName` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
junction table
pretty standard stuff