mel150
asked on
Sucessful query but errors
hi, Experts!
I have a successful query, but I am still getting errors.
If I run this query in Query Analyzer:
SELECT prod.name, prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod
INNER JOIN [sizes] size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
I get the response I want:
Name Description size2_price size4_price
But if I try to pull it to a webpage:
<table width="100%" border="1" cellspacing="2" cellpadding="2" align="left">
<tr><td><b>Menu Item</b></td><td><b>2 servings</b></td><td><b>4 servings</b></td></tr>
<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</c
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</c
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</c
</table>
I get an error: Variable SIZE2_PRICE is undefined.
I am pullng from two tables:
products:
columns: id, name, description
sizes:
columns: product_id (links to id in table products), code indicates size2 or size4), size2_price and size4_price.
What I want is:
name description size2_price size4_price in a table.
Any thoughts? Do you need more information? Thanks!
I have a successful query, but I am still getting errors.
If I run this query in Query Analyzer:
SELECT prod.name, prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod
INNER JOIN [sizes] size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
I get the response I want:
Name Description size2_price size4_price
But if I try to pull it to a webpage:
<table width="100%" border="1" cellspacing="2" cellpadding="2" align="left">
<tr><td><b>Menu Item</b></td><td><b>2 servings</b></td><td><b>4 servings</b></td></tr>
<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</c
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</c
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</c
</table>
I get an error: Variable SIZE2_PRICE is undefined.
I am pullng from two tables:
products:
columns: id, name, description
sizes:
columns: product_id (links to id in table products), code indicates size2 or size4), size2_price and size4_price.
What I want is:
name description size2_price size4_price in a table.
Any thoughts? Do you need more information? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That's because your selecting size4.Price
but you haven't joined in the size4 table, there is no way this query would have worked in Query Analyser.
but you haven't joined in the size4 table, there is no way this query would have worked in Query Analyser.
ASKER
Sorry, Plucka, you're right of course-this is the entire query that works in query analyzer:
SELECT prod.name, prod.description, prod.active, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod
INNER JOIN [sizes] size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN [sizes] size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
SELECT prod.name, prod.description, prod.active, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod
INNER JOIN [sizes] size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN [sizes] size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
ASKER
That's it, Plucka! Thanks!! When I do this;
<CFQUERY DATASOURCE="#request.maind
SELECT prod.name, prod.description, size2.Price AS size_2_Price, size4.Price AS size_4_Price
FROM products prod
INNER JOIN sizes size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN [sizes] size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
</CFQUERY>
It works !!!!!
<CFQUERY DATASOURCE="#request.maind
SELECT prod.name, prod.description, size2.Price AS size_2_Price, size4.Price AS size_4_Price
FROM products prod
INNER JOIN sizes size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN [sizes] size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
</CFQUERY>
It works !!!!!
SELECT prod.name, prod.description, prod.active, size2.Price AS Size_2_Price, size4.Price AS Size_4_Price
FROM products prod
INNER JOIN sizes size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN sizes size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
Will work, the problem is that you are using AS names with spaces, Coldfusion can't access them, thus i've put the _'s in.
See how you go
FROM products prod
INNER JOIN sizes size2
ON prod.ID = size2.Product_ID
AND size2.code = 'Size2'
INNER JOIN sizes size4
ON prod.ID = size4.Product_ID
AND size4.code = 'Size4'
Where prod.active=1
Will work, the problem is that you are using AS names with spaces, Coldfusion can't access them, thus i've put the _'s in.
See how you go
Yep,
Beat me to the punch, well done.
Beat me to the punch, well done.
ASKER
I tried that, but I get an error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix 'size4' does not match with a table name or alias name used in the query.