ColdFusion

how to populate a cfselect box depending upon the selection on raido button

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2009

Posts: 44

Reputation: cartman714 can only hope to improve

Solved Threads: 3

cartman714 cartman714 is offline

Offline

Light Poster

Re: how to populate a cfselect box depending upon the selection on raido button

#11

Jun 18th, 2009

thx

http://www.OMGretards.com

•

Join Date: Mar 2008

Posts: 11

Reputation: macslayer is an unknown quantity at this point

Solved Threads: 1

macslayer

Offline

Newbie Poster

Re: how to populate a cfselect box depending upon the selection on raido button

#12

Jul 23rd, 2009

•

Originally Posted by nagkar

Thanks the given sql is working ...it gave me correct data ..but now the problem is that it is displaying same data multipile times.i.e

ingredient........lotids............d_amount.......d_unit

xyz...............5t6y
xyz................7yh8
xyz................98uj

uvw.................9jnu

instead of that i need to display it as

xyz...............5t6y
..................7yh8
...................98uj

uvw.................9jnu

You are going to have to control the grouping of the xyz data by either testing for changes in some control variable assigned that value, or using built-in's of whatever flavor of sql your are using.
For instance, if your using mySql you could use group_concat
something like:
select table.*, GROUP_CONCAT(concat('Lot:',lotids,'Amt:',d_amount,'unit:',d_unit),'||') as big_lots
from table
left outer join ....
where ....
group by ingredient
having (table.whateverfield like 'whatever')

that give you one row per ingredient with a field called big_lots aggregating all the lots into it, delimited by || which you have to parse it out of to display nicely etc.. Anyway, it's an idea for which there are lots of solutions..

Bob

•

Join Date: Jul 2009

Posts: 5

Reputation: gklandes is an unknown quantity at this point

Solved Threads: 0

gklandes

Offline

Newbie Poster

Re: how to populate a cfselect box depending upon the selection on raido button

#13

Jul 24th, 2009

in the sql, try replacing "inner join" (which will return all matching rows from both tables) to "LEFT OUTER JOIN" which will essentially focus on the 1st table.

The problem, really might be that you have a "Many to Many" relationship in the DB. If the Recipe table links to the Lot table on ingredient and there are multiply rows for the same ingredient in both tables, your tables will never join properly. The solution is for the Recipe table to refer to a unique id in the lot table or add a table or two for better "normalization".

Assuming you are storing Products, Recipes, Ingredients and Lots, this is a possible schema:

tblProduct

productID (PK)
productName

tblRecipe

recipeID (PK)
productID (refers to tblProduct.productID)
ingredientID (refers to tblIngredient.ingredientID)
quantity

tblIngredient

ingredientID (PK)
ingredientName
lotID (refers to tblLot.lotID)
unit

tblLot

lotID (PK)
lotNumber

Then you could query one recipe with this SQL:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT r.quantity, i.ingredientName, i.unit, l.lotNumber
FROM tblProduct p LEFT OUTER JOIN 
tblRecipe r ON r.productID = p.productID LEFT OUTER JOIN
tblIngredient i ON i.ingredientID = r.ingredientID LEFT OUTER JOIN
tblLot l on l.lotID = i.lotID
WHERE p.productID = **some_product_id**
SELECT r.quantity, i.ingredientName, i.unit, l.lotNumber
FROM tblProduct p LEFT OUTER JOIN 
tblRecipe r ON r.productID = p.productID LEFT OUTER JOIN
tblIngredient i ON i.ingredientID = r.ingredientID LEFT OUTER JOIN
tblLot l on l.lotID = i.lotID
WHERE p.productID = **some_product_id**

•

Join Date: Mar 2008

Posts: 11

Reputation: macslayer is an unknown quantity at this point

Solved Threads: 1

macslayer

Offline

Newbie Poster

Re: how to populate a cfselect box depending upon the selection on raido button

#14

Jul 24th, 2009

I think his sql is working perfectly, as I read is that he is having a presentation issue, not relationship issue. His relationship looks like a one-to-many on ingredients to lots. He just does not know how to handle the repeating left hand column data joined to the right hand. So, he was looking for methods to not repeat the ingredient on the 1+nth lot. Anyway, that is how I read it.