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.
simple random:
SELECT *
FROM MOVIES
ORDER BY RAND()
LIMIT 3;
advance random:
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
