• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td></tr>


</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!
0
mel150
Asked:
mel150
  • 4
  • 3
1 Solution
 
PluckaCommented:
mel150,

Change your query to this

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'

Then you can access the variables in CF using their AS names ie size_2_Price.

Regards
Plucka
0
 
mel150Author Commented:
Hi, Plucka,

I tried that, but I get an error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix &apos;size4&apos; does not match with a table name or alias name used in the query.  
0
 
PluckaCommented:
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mel150Author Commented:
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
0
 
mel150Author Commented:
That's it, Plucka! Thanks!! When I do this;

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
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 !!!!!
0
 
PluckaCommented:
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
0
 
PluckaCommented:
Yep,

Beat me to the punch, well done.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now