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

Remove:

where mg.GenCode='Action' 
or mg.GenCode='Adven'

Add:

having find_in_set('Action', group_concat(DISTINCT g.GenCode))
and find_in_set('Adven', group_concat(DISTINCT g.GenCode))

This will get you started I think. Not sure if it's correct since you didn't show your table structures/data. Some tweaking may be necessary.

Sorry for the late reply. Thanks I'll give that a try.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.