As some of you might know I'm working on a movie database to help track my movie collection. For a while I was having trouble getting the output I wanted for that, but I finally figured out some sql code to help with that problem. I'm going to be working on writting a function to do the same thing with that, but not yet. I want to write an advance randomize query for those night you don't know what you want to watch, but know the genres that you feel like watching. I have a simple random function working, but I'm having issues figuring out an advance random function. I would appreciate any help that can be offered.
SELECT * FROM MOVIES ORDER BY RAND() LIMIT 3;
select DISTINCT m.Title, group_concat(DISTINCT g.Genre) as Genres, group_concat(DISTINCT f.Format) as Formats from MOVIES m left join MOVIEGENRES mg on m.Code=mg.MovieCode left join GENRES g on mg.GenCode=g.Code left join MOVIEFORMATS mf on m.Code=mf.MovieCode left join FORMATS f on mf.FormCode=f.Code where mg.GenCode='Action' or mg.GenCode='Adven' group by m.Code ORDER BY RAND() LIMIT 3;
The advance random is a quick and dirty code modification of what I used to get my results that I was having issues with before this post. This works and I'll be using something like this, but what I'm trying to figure out is how to get a different set of results.
So for this it gives me all the movies that have either action or adven, but I'm trying to write a query that would give me result for ones that give me only movies that have both in them.
current results: matrix Action terminator Action Eagle Eye Action, Adventure What I want to get: Eagle Eye Action, Adventure