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.

 

[yeppers]

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

Hey DodgyV,

THanks alot for trying but it didnt work. I keep getting "Error near AS movie_name, (SELECT..."

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 ).

Hey sdsinc ,

Sorry but your alterations didn't work either, I got the same error as the one above.

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..

Hey Dan,

I would've liked to do it all throug SQL to leave the script just that bit more optimized, but I think I'll have to do it through php.

Thanks everyone for your help, and rep left for all.

Rhett.

 

[adam_uk]

take a look at SQL joins

http://www.w3schools.com/sql/sql_join.asp

 

[Kate]

Rhett I assume the relationship between the two tables is
categories.id = movies.category
and that both fields are INT right ?
Perhaps you could post the table structure ?

 

[alanpaladka]

Have u tried table join sql queries?