Complex nested mySql query help needed.

[verbatim]

i have 4 tables in a db, to allow users rate certain recipes. they might not rate all recipes at one time. they might even go back and rate a recipe more than one time. there will also be at least 1, and up to 4 kids participating in voting for each recipe.

i would like to determine the first 100 moms to rate all X recipes using the date of submission of the vote of the final recipe as qualifier. (meaning a mom would get added to the list of 100 after she rates all recipes from the recipes table)

it seems it will either take a complex nested query, or several queries mixed in with php

below are the 4 tables.

any help would be appreciated.

recipes_db
-- recipe_id
-- recipe_name

mom_form
-- user_id
-- firstname
-- lastname
-- phonenumber
-- address
-- city
-- state
-- zipcode
-- timestamp

mom_comments
-- comment_id
-- user_id
-- recipe_id
-- favorite_recipe
-- moms_comments
-- childname1
-- childage1
-- child_comments1
-- childname2
-- childage2
-- child_comments2
-- childname3
-- childage3
-- child_comments3
-- childname4
-- childage4
-- child_comments4
-- timestamp

mom_votes
-- votes_id
-- rating
-- user_id
-- recipe_id
-- timestamp

[chaarmann]

You posted your assignment, fine. But did you also read the forum guideline?
So what's your question? How can we help?
Just post the SQL or code where you have difficulties with.

i have 4 tables in a db, to allow users rate certain recipes. they might not rate all recipes at one time. they might even go back and rate a recipe more than one time. there will also be at least 1, and up to 4 kids participating in voting for each recipe.

i would like to determine the first 100 moms to rate all X recipes using the date of submission of the vote of the final recipe as qualifier. (meaning a mom would get added to the list of 100 after she rates all recipes from the recipes table)

it seems it will either take a complex nested query, or several queries mixed in with php

below are the 4 tables.

any help would be appreciated.

recipes_db
-- recipe_id
-- recipe_name

mom_form
-- user_id
-- firstname
-- lastname
-- phonenumber
-- address
-- city
-- state
-- zipcode
-- timestamp

mom_comments
-- comment_id
-- user_id
-- recipe_id
-- favorite_recipe
-- moms_comments
-- childname1
-- childage1
-- child_comments1
-- childname2
-- childage2
-- child_comments2
-- childname3
-- childage3
-- child_comments3
-- childname4
-- childage4
-- child_comments4
-- timestamp

mom_votes
-- votes_id
-- rating
-- user_id
-- recipe_id
-- timestamp

[ronverdonk]

Just subscribing.

Ronald

[verbatim]

i was wondering if anyone might be able to determine a complex query based on the tables above, or if a concensus might be to break it up into smaller queries, the results of which might then be examined together with php.

my query: find the first 100 moms who rate X recipes as determined by the timestamp of the last (last for that user) submitted rating. the user might not rate all recipe in one sitting. they might rate them over several days.

So if there are 3 recipes to rate, a user enters (or doesn't enter) the list of 100 according to the timestamp of the third entry.

as i mentioned previously, i'm not sure if this can be accomplished in large complex (nested?) query, or it will require several separate queries.

i haven't really written any queries yet. I have written a few small queries that might do one part or other of the larger goal. the one or two attempts i have made at a large unified query, have not at all been successful and are not yet even worth posting.

thanks for any help.

[chaarmann]

i was wondering if anyone might be able to determine a complex query based on the tables above, or if a concensus might be to break it up into smaller queries, the results of which might then be examined together with php.

my query: find the first 100 moms who rate X recipes as determined by the timestamp of the last (last for that user) submitted rating. the user might not rate all recipe in one sitting. they might rate them over several days.

So if there are 3 recipes to rate, a user enters (or doesn't enter) the list of 100 according to the timestamp of the third entry.

as i mentioned previously, i'm not sure if this can be accomplished in large complex (nested?) query, or it will require several separate queries.

i haven't really written any queries yet. I have written a few small queries that might do one part or other of the larger goal. the one or two attempts i have made at a large unified query, have not at all been successful and are not yet even worth posting.

thanks for any help.

You can write all in one huge SQl, but I wouldn't do it. Because it makes the program very hard to understand for people who come after you and need to change the code. I have enough experience that I can tell you that even you would not understand your own SQL anymore if you look at it a year later or so. Also it makes it run very slow and puts a heavy load on the database.
Having many small SQLs instead are much, much better.
Just develop them and post them one by one if you have problems with them.
I can help you if you are stuck, for example if you don't know how to code the "find the first 100 moms" condition.
Then I would answer you, make the SQL that gets them all, let's call it "yourSqlStateme nt " and wrap around:

select * from ( yourSqlStatement ) where rownum <= 100;

But I will not give you complete solutions, only help. Because it's your work and you will not learn otherwise.