SQL multiple SELECT

[yeppers]

Hey all,

Well I've run into a bit of trouble with SQL.

I have two tables as shown below:

CATEGORIES
id category
1 action
2 drama
3 comedy

MOVIES
id category movie
1 action Movie Name 1
2 action Movie Name 2
3 action Movie Name 3
4 action Movie Name 4
5 action Movie Name 5
6 action Movie Name 6
7 action Movie Name 7
8 action Movie Name 8
9 action Movie Name 9
10 action Movie Name 10
11 comedy Movie Name 11
12 drama Movie Name 12
13 drama Movie Name 13
etc etc etc

What I would like to do is select 6 movies from each category (There are a lot more rows in my database than that) but I want to do it in one SQL query.

Anyone have some idea how?

Thanks all,
Rhett.

 

[Barrucadu]

Not too sure, but just seperate your queries with semicolons.

 

[yeppers]

Not too sure, but just seperate your queries with semicolons.

Hey Mikor,

Thanks for your help anyway.

But i'm looking to do it in just the one query, I know this can be done in seperate queries, but i'm pretty positive there's a slightly more optimized way to do it, i've been looking at sub selects and things, but just can't get what I need.

Thanks, Rhett.

 

[Noobie]

I suggest using the category_id in the category field of your movie table instead of the actual text.
I'm not sure of the SQL for what you ask, but it can be done with 2 queries and some php

 

[yeppers]

I suggest using the category_id in the category field of your movie table instead of the actual text.
I'm not sure of the SQL for what you ask, but it can be done with 2 queries and some php

Hey Noobie,

Yes I have used the category's id in my table, the one I placed here is simply an example. I know it can be done with multiple queries also, but I just need to know how to do it using sub selects or some other SQL coding, I'm pretty sure it can be with advanced SQL knowledge.

Thanks anyway though,
Rhett.

 

[TwistMyArm]

Now, all assuming you're using MySQL:

To limit your request to just 6 rows, you can add 'LIMIT 6' to the end of your query.

If you want 6 random rows, then that's something different. As far as I can tell, MySQL doesn't have a native RANDOM function: there are 'hacks' out there, but they generally get slow when you reach large numbers of rows. If you want random, the best thing to do is grab them all and then get 6 randoms within PHP.

 

[yeppers]

Now, all assuming you're using MySQL:

To limit your request to just 6 rows, you can add 'LIMIT 6' to the end of your query.

If you want 6 random rows, then that's something different. As far as I can tell, MySQL doesn't have a native RANDOM function: there are 'hacks' out there, but they generally get slow when you reach large numbers of rows. If you want random, the best thing to do is grab them all and then get 6 randoms within PHP.

Hey TwistMyArm,

If I use the LIMIT command that will simply select a maximum of 6 games altogether, i'm looking to grab 6 games from each different category. BUT I cannot just write in each category, as this is going to be a sold script, where people can add and remove categories from the category table, this is why I need the MySQL to first get the categories from category table, and then select 6 games from each category and give them to me.

Know what I mean?

Thanks, Rhett.

 

[Dan]

Untested:

$query  = "SELECT * FROM MOVIES";
$result = mysql_query($query);

$movies = array();

while ($row = mysql_fetch_assoc($result)) {
	if (count($movies[$row['category']]) != 6) {
		$movies[$row['category']][$row['id']]       = $row['id'];
		$movies[$row['category']][$row['category']] = $row['category'];
		$movies[$row['category']][$row['movie']]    = $row['movie'];
	}
}

I'm not sure how to do it better/easier than that..

 

[baxter]

You could use a union select but that basically is doing multiple queries. Dan's code above is how I'd do it..

 

[Noobie]

Any reason why it must be 1 query?

Select * categories
foreach category {
Select 6 images
}

 

[Scott]

Any reason why it must be 1 query?

Select * categories
foreach category {
Select 6 images
}

If there were 30 categories that would be a lot of queries.

 

[Dan]

If there were 30 categories that would be a lot of queries.

Caching :tu:

 

[DodgyV]

Assuming you are using MySQL, I think this is what you are looking for

SELECT
movies.id AS movie_id,
movies.name AS movie_name,
(
SELECT
categories.category
FROM
categories
WHERE
categories.category = movies.category
ORDER BY RAND() LIMIT 6
) AS movie_category
FROM
movies

Hope this helps

 

[Dan]

Assuming you are using MySQL, I think this is what you are looking for

SELECT
	movies.id AS movie_id,
	movies.name AS movie_name,
	(
		SELECT
			categories.category
		FROM
			categories
		WHERE
			categories.category = movies.category
		ORDER BY RAND() LIMIT 6
	) AS movie_category
FROM
	movies

Hope this helps

Wow at that query..
BillyConnite: tell us if that works.

 

[Noobie]

i think u need 4.1 but props to that for sure

and is that ca-ching or caching ...

 

[Dan]

Caching. Like cache-ing.

 

[Kate]

Actually Adnan is close (BTW welcome to NP :wave: )
Putting the rights fields together I believe we have something like (not tested):

SELECT movies.id AS movie_id, movies.movie AS movie_name, (
SELECT categories.category
FROM categories
WHERE categories.id = movies.category
LIMIT 6
) AS movie_category
FROM movies

ORDER BY RAND( ) will pickup records in random order (if desired ).

 

[DodgyV]

I wasn't quite sure which 6 movies from the list were wanted, so I figured showing 6 random ones would give the general idea of how the query works

Actually Adnan is close (BTW welcome to NP :wave: )
Putting the rights fields together I believe we have something like (not tested):



ORDER BY RAND( ) will pickup records in random order (if desired ).

 

[wasistdas]

I dont know how it is in PHP. In ColdFusion it is quite easy with QoQ - Query of Query. You select all records you need and then just select from the selection made.

 

[yeppers]

Hey all!

Thanks for all your suggestions, I've been busy lately and haven't been able to try these out yet, but I'll let you know how they all go.

I'll have a chance to thank you all later .

Thanks again,
Rhett.