Query Help! Please!

[kingjes]

I have a question for on of you uber l33t SQL dudes.. its a detailed question, so the best way i could present it was to make a little page for you to see the screen shot and the sql I am trying.

Im uber stuck and could use your help! Im burnt on this been at it for 2-3 hours already!

Please, take a moment and help a brotha out!
thankyou very much!
-jes

http://www.mercednutrition.org/_admin/test_shopping_list.cfm

 

[maswien]

A few thoughts:

1. The relationships among tables are not specified, there are two tables between the recipe and indigrents: recipe_signed_for and recipe_signed_for_indigrent_needed, which one should be used when join the recipe table to indigrent table?

2. Looks like you are using access, the results you want can be achieved using access report, all you need to do is put the column recipe_name into higher level, then you will get recipe_name and the indigrent under it.

3. For the total, it's pretty straight forward, you just group by the indigrent and sum the quantity should be OK.

 

[mrdenny]

The first part will need to be handled through the front end.

I don't know the specifics of cold fusion, but in asp I would declare a variable and set the var to = the first recipe name. Then as I would loop through the records I wouldn't display the recipe name again until I got to a new recipe name.

For the shopping list you can either do the math on the front end, our you'll need to run another query against the database. It will be the same basic query as the first one, but only showing the recipe_ingredients.active_ingredient, recipe_ingredients.ingredient_unit and sum(recipe_ingredients.ingredient_quantity).

The last part will be the same basic query as the first query put with the extra fields you are looking for added in. Then you do the variable trick within the loop to make it look like that. It's all front end work. All SQL will return to you is a recordset in a table format. If you want to make it look cooler, that's all frontend stuff.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[DotNetGnat]

for the first part...i have already posted the code in the asp forums...here is the link...hope it helps...

thread333-1113977

-DNG

 

[kingjes]

hay all, thank you very much for your fast reply.

re:
1. The relationships among tables are not specified, there are two tables between the recipe and indigrents: recipe_signed_for and recipe_signed_for_indigrent_needed, which one should be used when join the recipe table to indigrent table?

recipe_ingredients.recipe_ingredient_id is the ID of the recipes_signed_up_for_ingredients_needed.ingredient_id

ie - the recipe_ingredients.recipe_ingredient_id for Honey is ID 1 so the recipes_signed_up_for_ingredients_needed.ingredient_id is 1

this is so the users can specify weather or not they need a particular ingrediant. thereis also a recipe_ingredients.active_ingredient y/n so if they dont need it its 0 or no and if they do its 1 or yes

Is it possible someone can show me a sample of the totals?

are my joins right/optimized as they can be?


thank you all for your help!! I got about 2 weeks to wrap this whole app up and its getting close! haha

any more suggestions are very welcome! Tomorrow I will tryout the loop. I may try some sort of array or struct method but im not super great with them eaither..
-jes