SQL multiple SELECT

BillyConnite · 08-28-2006, 09:35 PM

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 · 08-28-2006, 10:22 PM

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

BillyConnite · 08-29-2006, 12:30 AM

Quote:

Originally Posted by Mikor

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 · 08-29-2006, 12:59 AM

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

BillyConnite · 08-29-2006, 03:02 AM

Quote:

Originally Posted by 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

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 · 08-29-2006, 04:54 AM

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.

BillyConnite · 08-29-2006, 06:11 AM

Quote:

Originally Posted by 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.

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 · 08-29-2006, 08:15 AM

Untested:

PHP Code:

  $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 · 08-29-2006, 09:32 AM

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

Noobie · 08-29-2006, 10:38 AM

Any reason why it must be 1 query?

Select * categories
foreach category {
Select 6 images
}

Scott · 08-29-2006, 01:53 PM

Quote:

Originally Posted by Noobie

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 · 08-29-2006, 02:09 PM

Quote:

Originally Posted by Scott

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

Caching

DodgyV · 08-29-2006, 02:18 PM

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 · 08-29-2006, 02:56 PM

Quote:

Originally Posted by DodgyV

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

Code:

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 · 08-29-2006, 03:04 PM

i think u need 4.1 but props to that for sure

and is that ca-ching or caching ...

Dan · 08-29-2006, 03:15 PM

Caching. Like cache-ing.

sdsinc · 08-29-2006, 03:28 PM

Actually Adnan is close (BTW welcome to NP

)
Putting the rights fields together I believe we have something like (not tested):

Quote:

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 · 08-29-2006, 03:40 PM

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

Quote:

Originally Posted by sdsinc

Actually Adnan is close (BTW welcome to NP

)
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 · 08-29-2006, 05:06 PM

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.

BillyConnite · 08-31-2006, 06:26 PM

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.

BillyConnite · 09-02-2006, 12:48 AM

Quote:

Originally Posted by 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

Hey DodgyV,

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

Quote:

Originally Posted by sdsinc

Actually Adnan is close (BTW welcome to NP

)
Putting the rights fields together I believe we have something like (not tested):

Quote:

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.

Quote:

Originally Posted by Dan Friedman

Untested:

PHP Code:

  $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 · 09-02-2006, 01:51 AM

take a look at SQL joins

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

sdsinc · 09-02-2006, 11:49 AM

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 · 09-03-2006, 10:30 AM

Have u tried table join sql queries?

08-28-2006, 09:35 PM	#1 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,107 47.00 NP$ (Donate)	SQL multiple SELECT 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. __________________ <?php if(1===1){ $computer="fine."; }else{ $computer="broken."; } echo "Your computer is ".$computer; ?>

08-28-2006, 10:22 PM	#2 (permalink)
Barrucadu Barru. Join Date: Aug 2005 Location: East Yorkshire, England Posts: 2,731 78.50 NP$ (Donate)	Not too sure, but just seperate your queries with semicolons. __________________ Website \| Blog \| Uzbl (my branch) 9thSeptember2009.com

08-29-2006, 12:59 AM	#4 (permalink)
Noobie NamePros Regular Join Date: Feb 2006 Location: Montreal, Quebec, Canada Posts: 324 66.75 NP$ (Donate)	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 __________________ Goldkey.com is a scam What's your BMI? \| Timestamp Generator

08-29-2006, 04:54 AM	#6 (permalink)
TwistMyArm NamePros Member Join Date: May 2006 Posts: 160 81.00 NP$ (Donate)	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. __________________ Free Fuel Efficiency Calculator \| CMS Book Store

08-29-2006, 08:15 AM	#8 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,801 56.00 NP$ (Donate)	Untested: PHP Code: `$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.. Last edited by Dan Friedman; 08-29-2006 at 09:02 AM.

08-29-2006, 09:32 AM	#9 (permalink)
baxter NamePros Regular Join Date: Apr 2006 Posts: 289 1,990.00 NP$ (Donate)	You could use a union select but that basically is doing multiple queries. Dan's code above is how I'd do it..

08-29-2006, 10:38 AM	#10 (permalink)
Noobie NamePros Regular Join Date: Feb 2006 Location: Montreal, Quebec, Canada Posts: 324 66.75 NP$ (Donate)	Any reason why it must be 1 query? Select * categories foreach category { Select 6 images } __________________ Goldkey.com is a scam What's your BMI? \| Timestamp Generator

08-29-2006, 02:18 PM	#13 (permalink)
DodgyV New Member Join Date: Aug 2006 Location: Chelmsford, UK Posts: 7 0.00 NP$ (Donate)	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

08-29-2006, 03:04 PM	#15 (permalink)
Noobie NamePros Regular Join Date: Feb 2006 Location: Montreal, Quebec, Canada Posts: 324 66.75 NP$ (Donate)	i think u need 4.1 but props to that for sure and is that ca-ching or caching ... __________________ Goldkey.com is a scam What's your BMI? \| Timestamp Generator

08-29-2006, 03:15 PM	#16 (permalink)
Dan Buy my domains. Join Date: Feb 2006 Posts: 2,801 56.00 NP$ (Donate)	Caching. Like cache-ing.

08-29-2006, 05:06 PM	#19 (permalink)
wasistdas Senior Member Join Date: Oct 2005 Location: Ukraine Posts: 1,559 152.20 NP$ (Donate)	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. __________________ The Official Dead End Place: WWW.TODEP.COM. The best search engine ever. WWW.NULLSEARCH.COM For Sale: Loan.IM, EnjoyArcade.com, Arcadoid.com

08-31-2006, 06:26 PM	#20 (permalink)
BillyConnite Join Date: Jul 2005 Location: Coffs H, Australia Posts: 3,107 47.00 NP$ (Donate)	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. __________________ <?php if(1===1){ $computer="fine."; }else{ $computer="broken."; } echo "Your computer is ".$computer; ?>

09-02-2006, 01:51 AM	#22 (permalink)
adam_uk Senior Member Join Date: May 2003 Posts: 2,211 6,170.25 NP$ (Donate)	take a look at SQL joins http://www.w3schools.com/sql/sql_join.asp

09-02-2006, 11:49 AM	#23 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,285 1,095.94 NP$ (Donate)	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 ? __________________ Buy now - MassDeveloper.com $500

09-03-2006, 10:30 AM	#24 (permalink)
alanpaladka Account Closed Join Date: Oct 2005 Location: India Posts: 299 142.65 NP$ (Donate)	Have u tried table join sql queries?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)