Display ID as name, not number?

[Diggum1]

Hi everyone,

I have a query that returns rows for search results. The first column is a Category ID that, in the output, is displaying the ID number. I'd like the output to display the name of the category, not the ID number (see below)

This is my query:
SELECT *
FROM Products
WHERE 0=0
AND CatID = #FORM.CatID#

So, Instead of:
Category | 3
Name | Bill Nye
Size | Large

I'd like:
Category | Shirts
Name | Bill Nye
Size | Large

Thanks
Rick

 

[Luzian]

You only get data that you ask for

I'm assuming there is a Categories table, if so, then your query should look similar to this:

SELECT Products.Name AS [ProductName],
Products.Size AS [ProductSize],
Categories.[Name] AS [CategoryName]
FROM Products
JOIN Categories
ON Products.[CatID] = Categories.[CatID]
WHERE Categories.CatID = #FORM.CatID#

Most likey, this code will not exactly work, you need to know your table structure first.

 

[Diggum1]

Yes, there is a Categories table, which has a FK in the Products table. This is my complete query:


<CFQUERY NAME="search_results" DATASOURCE="wioDB">
SELECT *
FROM Products
WHERE 0=0

<CFIF FORM.Designer IS NOT "">
AND Designer LIKE '%#FORM.Designer#%'
</CFIF>

<CFIF FORM.DressSize IS NOT "">
AND DressSize LIKE '%#FORM.DressSize#%'
</CFIF>

<CFIF FORM.Price IS NOT "">
AND Price <= #FORM.Price#
</CFIF>

<CFIF FORM.CatID IS NOT "">
AND CatID = #FORM.CatID#
</CFIF>
ORDER BY Price
</CFQUERY>

Trying your example now...

Thanks
Rick

 

[Diggum1]

Hmm. So far no luck...

 

[GUJUm0deL]

Diggum1, you should never do SELECT *, the better practice is to type up the column names (no matter how many fields you need).

Can you post the query/code you're using now? In the post above you provided the query you're using, but you have a Categories table which you need to join to get the result set you want. Please post the example you tried after Luzian's help.

Thanks.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[Luzian]

Without your table structures, we can't help you in the way you want us to. My code was not meant to be copied & pasted, it was based on an assumption of your table structure in which none of us know.

 

[Diggum1]

Thanks for your help... I realize your code was just an example. Let me get home tonight and make sure my table names are correct before I waste more of your time