Question

Select a distinct column, and non-distinct columns

Asked by: mel150

Hi- I have this:

 SELECT    Distinct name, description
 FROM      products

But I don't want the column 'description' to be distinct. (In fact, it won't let me make it distinct, because it is text. name is varchar).

 Is there a way to do that, select one column distinctly and another..ummm... indistinctly? :)

Thanks!

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2006-05-29 at 13:02:13ID21867427
Tags

distinct

,

column

,

select

Topic

MS SQL Server

Participating Experts
5
Points
500
Comments
58

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Distinct?
    when I use "select distinct item1, item2 from table_name" , the result is distinct item1 or distinct combination of item1 and item2. Thank you. Shane
  2. Distinct
    I want to select several fields from a table using distinct but I only want the first three to use the distinct. Is there a way to do this or would I have to use sub query?

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: matthewspatrickPosted on 2006-05-29 at 13:16:12ID: 16785780

Hi mel150,

Your question does not really make sense.  If there are three records with the same name but the description columns
all have different entries, wouldn't you want to return all three records?

Regards,

Patrick

 

by: MikeWalshPosted on 2006-05-29 at 13:17:58ID: 16785786

Mel...


Can you show a sample of your table with some data (use data that best illustrates the case you are trying to accomplish) and a sample of what you want your result set to be like?

 

by: mel150Posted on 2006-05-29 at 13:18:38ID: 16785787

but it won't let me select description, because it is a datatype text.

 

by: MikeWalshPosted on 2006-05-29 at 13:21:13ID: 16785798

show us what you want and we can help

 

by: mel150Posted on 2006-05-29 at 13:29:23ID: 16785828

This is a little hard to explain, because I'm pretty inexperienced. I am pulling from two tables.

 In the second table, the item appears twice. That is why I want it to be distinct.

 So in products, I have:
2060|Bada|“Bada- Bing” Cherry Bread Pudding|This luscious pudding is to die for. We start with fresh French bread cubes and B& B soaked dark Bing Cherries. We put them together with the finest sugar and cream and bake; all you have to do is heat 1 minute and serve with our Crème Anglaise.

And in the table sizes, I have:
125567|size4|2060|4||15|0|0|0|0||0|0|0||15
125510|size2|2060|2||7.5|0|0|0|0||0|8|0|7.5|

I want the results to be:

|“Bada- Bing” Cherry Bread Pudding|This luscious pudding is to die for. We start with fresh French bread cubes and B& B soaked dark Bing Cherries. We put them together with the finest sugar and cream and bake; all you have to do is heat 1 minute and serve with our Crème Anglaise.|size2|size4

Not:
|“Bada- Bing” Cherry Bread Pudding|This luscious pudding is to die for. We start with fresh French bread cubes and B& B soaked dark Bing Cherries. We put them together with the finest sugar and cream and bake; all you have to do is heat 1 minute and serve with our Crème Anglaise.|size2|

|“Bada- Bing” Cherry Bread Pudding|This luscious pudding is to die for. We start with fresh French bread cubes and B& B soaked dark Bing Cherries. We put them together with the finest sugar and cream and bake; all you have to do is heat 1 minute and serve with our Crème Anglaise.|size4|

Does that make sense? And as I said, it won't let description be distinct. Thanks!


 

by: MikeWalshPosted on 2006-05-29 at 13:32:21ID: 16785844

I presume 2060 is what links them up..  (foreign key and primary key)...

I presume you only have one description in each products table..

Do you want all of that other info from the sizes table?

 

by: MikeWalshPosted on 2006-05-29 at 13:35:11ID: 16785853

The reason I ask, is because this looks different than the example query in your original query.. So I am little confused.

 

by: MikeWalshPosted on 2006-05-29 at 13:35:58ID: 16785856

<offtopic>I think we would actually need to sample the recipe to properly understand this problem ;-) sounds good... </offtopic>

 

by: mel150Posted on 2006-05-29 at 13:36:52ID: 16785861

Correct, 2060 is the link.

yep, only one desription, and only in the products table.

No, actually I only want sizes2_price and sizes4_price from the sizes table. Sorry, I mistyped before.

Thanks!!

 

by: mel150Posted on 2006-05-29 at 13:38:13ID: 16785868

<offtopic>LOL- I can't keep up with you guys! It does sound good, doesn't it? </offtopic>

 

by: MikeWalshPosted on 2006-05-29 at 13:39:20ID: 16785873

OK.. I don't have access to SQL right now but try this(I apologize for syntax, normally I check queries but I am not connected to the network so no server to test on)

SELECT prod.description, size1.Price [Size 2 Price], size2.Price [Size 4 Price]
FROM PRODUCTS prod

INNER JOIN TableSize size1
 ON prod.PrimaryKey = size1.PrimaryKey

INNER JOIN TableSize size2
  ON prod.PrimaryKey = size2.PrimaryKey

You will need to change the columns and tables appropriately.

 

by: MikeWalshPosted on 2006-05-29 at 13:39:59ID: 16785874

It sounds great..

I also just thought.. Are there always two prices or can there be more or less?

 

by: mel150Posted on 2006-05-29 at 13:40:37ID: 16785875

There are always two prices, for two servings and four servings.

 

by: MikeWalshPosted on 2006-05-29 at 13:42:20ID: 16785882

Sorry... In a haze today (was out doing tilling in the heat..)

My solution won't actually do anything for you there.. You'd have to add something like

SELECT prod.description, size1.Price [Size 2 Price], size2.Price [Size 4 Price]
FROM PRODUCTS prod

INNER JOIN TableSize size1
 ON prod.PrimaryKey = size1.PrimaryKey
 AND size1.SizeType = 'Size2'

INNER JOIN TableSize size2
  ON prod.PrimaryKey = size2.PrimaryKey
  AND size2.SizeType = 'Size4'

This is not the best solution the more I think about it.. It will give you results you desire when you have two prices, but if you can have more or less we need to do something a little different.

 

by: MikeWalshPosted on 2006-05-29 at 13:43:29ID: 16785889

OK.. Then if there area always two prices never more and never less, and that column in the sizes table always says 'Sizes2' or 'Sizes4'
or you can use that number type column that says "2" or "4"

I just didn't see your column names.

 

by: mel150Posted on 2006-05-29 at 13:45:46ID: 16785895

Thanks very much-I will try this tonight!

 

by: MikeWalshPosted on 2006-05-29 at 13:48:09ID: 16785905

ok.. but you will need to replace everything with your tables and columns.. Not sure how much you know about SQL.. You said you didn't know a lot, so sorry if this is insulting just want to give you all of the info you need..

So where I said "PrimaryKey" you will need to replace with the actual column names from each table, need to change your table names, and need to change the "sizetype" to actually be the name of the column..

If you want any other information returned, you can put it in the select list, just prefix it with the table it is coming from's alias.

 

by: mel150Posted on 2006-05-29 at 13:49:39ID: 16785916

OK-thanks! Not insulting at all, I really am new at this. :)

 

by: MikeWalshPosted on 2006-05-29 at 13:50:35ID: 16785921

no problem. good luck and check back before you run it, you may get some other ways to do it by people who were less lazy than me.. But since there are only two prices, what I gave you should work just fine.

 

by: anyoneisPosted on 2006-05-29 at 14:28:13ID: 16786054

How about:

SELECT DISTINCT size, SUBSTRING(description, 0, 4096)
FROM ...


David

 

by: MikeWalshPosted on 2006-05-29 at 14:30:55ID: 16786060

David,

I think that the results are in different tables from further on in the conversation with Mel.. That is an interesting idea to use the substring, but what if the text data is similar for the first 4096 characters??

 

by: mel150Posted on 2006-05-29 at 18:31:19ID: 16786798

Hi- Sorry, I'll have to check this with you to be sure I understand..

So the tables are:
products (columns I am using are are id, name, description)
sizes (columns I am using are product_id, which links to id, size2_price, size4_price- but each id from the product table appears twice in the sizes table).

So if the product table has an id of 2060, the sizes table will have:
Id=23774, product_id=2060, size2_price=8
Id =23775, product_id=2060, size4_price=16


And I want the outcome to say:
Name|Description|Size2_price|Size4_price

What I am seeing now is:
Name|Description|Size2_price
Name|Description|Size4_price

Is that a little better explanation? Sorry I was a little obtuse earlier. Will your solution above still work, do you think?

Thanks again!

 

by: HuyBDPosted on 2006-05-29 at 19:15:52ID: 16786913


Assume that table sizes have 2 fields, 1 store type of size and other store value, I add sub query to avoid select text filed in group clause
try this!


select sizes.id,name, select top 1 description from products as p where p.id=sizes.product_id) as description,
sum(case when sizetype='size2_price' then sizevalue else 0 end) as size2_price,
sum(case when sizetype='size4_price' then sizevalue else 0 end) as size4_price
from sizes
inner join products on sizes.product_id=products.id
group by sizes.id,name

 

by: mel150Posted on 2006-05-29 at 19:25:33ID: 16786941

Hi, HuyBD,

 Wow- that looks complicated to me! I tried a straight copy and paste, and the browser didn't like it.. If I understood more I might be able to get it to work, I apologize for not being more adept. It looks like you have all the fields right as far as my column names go, but I get an error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword &apos;select&apos;.  
 

 

by: HuyBDPosted on 2006-05-29 at 19:29:21ID: 16786956

I forget to open the parentheses

select sizes.id,name, (select top 1 description from products as p where p.id=sizes.product_id) as description,
sum(case when sizetype='size2_price' then sizevalue else 0 end) as size2_price,
sum(case when sizetype='size4_price' then sizevalue else 0 end) as size4_price
from sizes
inner join products on sizes.product_id=products.id
group by sizes.id,name

 

by: mel150Posted on 2006-05-29 at 19:34:55ID: 16786971

Hmmm.. Now I get: Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name &apos;name&apos;.  


Hey- maybe that should be group by sizes.id, products.name?

 

by: mel150Posted on 2006-05-29 at 19:37:35ID: 16786979

Hmm. I think the problem is that there is no column "name" in the sizes table. That column is in the products table.

So I'm getting an error on this: select sizes.id,name,

 

by: HuyBDPosted on 2006-05-29 at 20:41:07ID: 16787163

>>Hey- maybe that should be group by sizes.id, products.name?
yes, you can add all prefix in select

select sizes.id,products.name, (select top 1 description from products as p where p.id=sizes.product_id) as description,
sum(case when sizes.sizetype='size2_price' then sizes.sizevalue else 0 end) as size2_price,
sum(case when sizes.sizetype='size4_price' then sizes.sizevalue else 0 end) as size4_price
from sizes
inner join products on sizes.product_id=products.id
group by sizes.id, products.name

 

by: mel150Posted on 2006-05-29 at 20:56:04ID: 16787208

hmm. did that, and got: Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name &apos;sizevalue&apos;.

Sorry to keep coming back to you...

 

by: HuyBDPosted on 2006-05-29 at 21:09:48ID: 16787253

As I say, Assume that table sizes have 2 fields, "sizetype" store type of size and "sizevalue" store value
You can change it to your actual field properly
Can you show all fields in table sizes?

 

by: mel150Posted on 2006-05-29 at 21:23:19ID: 16787287

Hi, HuyBD,  I apologize. I'm not understanding, although I'm sure you are right. Let me look at this again tomorrow when my mind is a little fresher. Thanks a lot for your help!!

 

by: imran_fastPosted on 2006-05-30 at 02:15:00ID: 16788392


/*create this function */

create function dbo.fn_getsize(@product_id int)
returns varchar(1000) as
begin

      declare @sizes varchar(1000), @size varchar(100)
      set @sizes =''
      declare c cursor for select size2orsize4_ColumnName from sizes where product_id = @product_id for read only
      open c
            fetch next from c into @size
                  while @@fetch_Status = 0
                  begin
                        set @sizes = ',' + @size
                        fetch next from c into @size
                  end
      close c
      deallocate c
      return @sizes
end
go
/*use this query to fetch the data
================================*/

select       [name],
      [description],
      dbo.fn_getsize(product_id)
from products

 

by: MikeWalshPosted on 2006-05-30 at 04:20:44ID: 16788875

Mel -

I thought in your original post to me you had a column in the sizes table that indicated if it was a 2 or a 4 type size.. There are no other columns in that table? If there are my second query will work without the function or anything else..

You wrote:

125567|size4|2060|4||15|0|0|0|0||0|0|0||15
125510|size2|2060|2||7.5|0|0|0|0||0|8|0|7.5|

for what was in the sizes table... If that |size4| is a column or the |4| is a column that indicates size, you can use my second query:

SELECT prod.description, size1.Price [Size 2 Price], size2.Price [Size 4 Price]
FROM PRODUCTS prod

INNER JOIN TableSize size1
 ON prod.PrimaryKey = size1.PrimaryKey
 AND size1.SizeType = 'Size2'

INNER JOIN TableSize size2
  ON prod.PrimaryKey = size2.PrimaryKey
  AND size2.SizeType = 'Size4'


"SizeType" is whatever column has the |Size4| or |Size2| in it. If there is such a column.
PrimaryKey is the key name in the Products table and the corresponding key in the sizes table/

TableSize is the table that has sizes in it

Everything else you can leave as is (assuming there is in fact a column with "size2" or "size4" in it.

 

by: mel150Posted on 2006-05-30 at 06:57:39ID: 16789745

I'm sorry to all- I haven't been giving the right information, and that has wasted some of your time.

Here are the exact column headers for table size, with two rows of data. It is confusing, because the way the former developers built it, there is a ROW that specifies size 2 or size 4, and a price in each row. But then I added COLUMNS size2_price and size4_price, becuase I wanted to call them for this project. And I copied over the price column to the size2_price and size_four price columns:

id |code| product_id |name| color| price| sale_price| cost| stock| sort_order| product_code| active |weight |oversize| size2_price |size4_price

125503 size2 1333 2 &nbsp; 13.33 0 0 0 0 SoyTend 0 0 0 13.33 &nbsp;
125534 size4 1333 4 &nbsp; 26.66 0 0 0 0 SoyTend 0 0 0 &nbsp; 26.26


 

by: MikeWalshPosted on 2006-05-30 at 07:21:55ID: 16789969

Ok...So now you ahve the price in two places. That is redundant and not the best form.. Where is the price always going to be accurate? in the "Size2_Price" and "Size4_Price" columns or in the "price" column? Looking at the above, it actually makes more sense NOT having the extra columns at the end and having two rows for each..

But anyway this should work:

SELECT prod.description, size2.Price [Size 2 Price], size4.Price [Size 4 Price]
FROM PRODUCTS prod

INNER JOIN TableSize size2
 ON prod.Product_ID = size2.Product_ID
 AND size2.Code = 'Size2'

INNER JOIN TableSize size4
  ON prod.Product_ID = size4.Product_ID
  AND size4.Code = 'Size4'

You may still need to change some columns and table names around.. Not sure exactly what the size table is called.

 

by: HuyBDPosted on 2006-05-30 at 18:24:36ID: 16794974

If size2_price and size4_price field is integer, try this

select sizes.id,products.name, (select top 1 description from products as p where p.id=sizes.product_id) as description,
sum(case when sizes.size2_price is not null then sizes.size2_price else 0 end) as size2_price,
sum(case when sizes.size4_price is not null then sizes.size4_price else 0 end) as size4_price
from sizes
inner join products on sizes.product_id=products.id
group by sizes.id, products.name

 

by: mel150Posted on 2006-05-31 at 06:51:49ID: 16798581

Hi, HuyBD,

Sorry, I get "The text, ntext, and image data types are invalid in this subquery or aggregate expression." Unfortunately desription column is text.  I have thought about changing that.

You know, maybe I'm going at this the wrong way. I need to think harder about this and see if I can simplify. Thanks so much for everyone's help, let's put this on hold for a bit while I unscramble my brain. :)

 

by: MikeWalshPosted on 2006-05-31 at 06:54:41ID: 16798607

mel - did you try and run my query yet? I think it gives you what you are asking, but definitely unscrambling the brain is a good start also :)

 

by: mel150Posted on 2006-05-31 at 08:05:14ID: 16799339

I did try- I got an error, and made some changes and got another error...  that's when I decided to unscramble. :)

 

by: MikeWalshPosted on 2006-05-31 at 08:15:54ID: 16799456

lol.. okay.. if you need help, report your error, it is probably something to do with the table names/column names .

 

by: HuyBDPosted on 2006-05-31 at 18:33:37ID: 16803952

Depend on you purpose, try convert text to varchar if possible

select sizes.id,products.name, (select top 1 cast(description as varchar) from products as p where p.id=sizes.product_id) as description,
sum(case when sizes.size2_price is not null then sizes.size2_price else 0 end) as size2_price,
sum(case when sizes.size4_price is not null then sizes.size4_price else 0 end) as size4_price
from sizes
inner join products on sizes.product_id=products.id
group by sizes.id, products.name

 

by: MikeWalshPosted on 2006-05-31 at 18:38:53ID: 16803972

SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [CHANGE THESE WORDS TO TABLE NAME WITH PRODUCTS KEEP BRACKETS] prod

INNER JOIN [CHANGE THESE WORDS TO TABLE NAME WITH SIZES KEEP BRACKETS] size2
 ON prod.Product_ID = size2.Product_ID
 AND size2.Code = 'Size2'

INNER JOIN [CHANGE THESE WORDS TO TABLE NAME WITH SIZES KEEP BRACKETS] size4
  ON prod.Product_ID = size4.Product_ID
  AND size4.Code = 'Size4'


This should work for you if you change the table names in the brackets above. Your products table that lists the "description" is called description above. if it is named differently in your environment change the prod.description to prod.COLUMNNAME (change columnname to proper column name)..

The codes for sizes are also assuming that the column name that has the text "Size4" or "Size2" is actually called "code" if it is not you need to change that in the "Size2.Code" and "Size4.code" change the word code to the actual column.

Lastly, it is also assuming that the Product_ID column is the same in each table.

 

by: mel150Posted on 2006-06-03 at 11:42:22ID: 16824319

hi again; you know what? I think I am confusing the issue, making it sound more complex than it really is; you guys have been phenomenal in your help so far; let's try one more thing. Here's the thing- this code below does exactly what I want it to do EXCEPT it doesn't relate the two tables:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
      SELECT      *
      FROM       products
      WHERE active='1'
      ORDER BY name
</CFQUERY>
<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_prices">
      SELECT      *
      FROM       sizes
</CFQUERY>

<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_prices" startrow=1 maxrows=1>$#price#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td></tr>

That gives me this result, but as I said, the prices aren't right becasue the two tables aren't linked in the query:

Menu Item                                                2 servings                4 servings                

Name and description of item one               $4.50                       $8.75
Name and description of item two               $15                          $29
etc...

 

by: MikeWalshPosted on 2006-06-03 at 11:44:40ID: 16824326

hmmm... I am a little more confused here..

Can you run the code I gave you above in Query Analyzer and see if the output is what you want. If errors come out, reply with the errors here.

My query links the tables and shows the column headers as necessary.

 

by: mel150Posted on 2006-06-04 at 09:43:09ID: 16827663

Thanks, Mike, I ran this code:
<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod

INNER JOIN [sizes] size2_price
 ON prod.ID = sizes.Product_ID
 AND sizes.size2_price = 'Size2'

INNER JOIN [sizes] size4_price
  ON prod.ID = sizes.Product_ID
  AND sizes.size4_price = 'Size4'

</CFQUERY>

And got these errors:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix &apos;size2&apos; does not match with a table name or alias name used in the query.  
 
The error occurred in F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
 
7 :       <link rel="stylesheet" href="../stylesheet.css">
8 : </head>
9 : <CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
10 : SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
11 : FROM [products] prod

 

--------------------------------------------------------------------------------
 
SQL    SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price] FROM [products] prod INNER JOIN [sizes] size2_price ON prod.ID = sizes.Product_ID AND sizes.size2_price = 'Size2' INNER JOIN [sizes] size4_price ON prod.ID = sizes.Product_ID AND sizes.size4_price = 'Size4'  
DATASOURCE   magicwww
VENDORERRORCODE   107
SQLSTATE   HY000
 

 

by: MikeWalshPosted on 2006-06-04 at 10:38:35ID: 16827884

you need to change it to this:

SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
FROM [products] prod

INNER JOIN [sizes] size2
 ON prod.ID = sizes.Product_ID
 AND sizes.size2_price = 'Size2'

INNER JOIN [sizes] size4
  ON prod.ID = sizes.Product_ID
  AND sizes.size4_price = 'Size4'

 

by: mel150Posted on 2006-06-04 at 10:52:49ID: 16827936

hmmm. now I get:

Error Occurred While Processing Request  
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix &apos;sizes&apos; does not match with a table name or alias name used in the query.  
 
The error occurred in F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
 
7 :       <link rel="stylesheet" href="../stylesheet.css">
8 : </head>
9 : <CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
10 : SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
11 : FROM [products] prod

 

--------------------------------------------------------------------------------
 
SQL    SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price] FROM [products] prod INNER JOIN [sizes] size2 ON prod.ID = sizes.Product_ID AND sizes.size2_price = 'Size2' INNER JOIN [sizes] size4 ON prod.ID = sizes.Product_ID AND sizes.size4_price = 'Size4'  
DATASOURCE   magicwww
VENDORERRORCODE   107
SQLSTATE   HY000
 
 But my table name is sizes.

 

by: MikeWalshPosted on 2006-06-04 at 10:55:16ID: 16827949

grr.. sorry my bad I didn't realize this was changed to... try this now:

SELECT 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.size2_price = 'Size2'

INNER JOIN [sizes] size4
  ON prod.ID = size4.Product_ID
  AND size4.size4_price = 'Size4'

 

by: mel150Posted on 2006-06-04 at 11:00:33ID: 16827970

Mike, I'm sorry- what a pain this is turning out to be for you! Now I get:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to float.  
 
The error occurred in F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 9
Called from F:\Inetpub\wwwroot\magickitchen_dev\catalog\print_menu.cfm: line 1
 
7 :       <link rel="stylesheet" href="../stylesheet.css">
8 : </head>
9 : <CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
10 : SELECT prod.description, size2.Price AS [Size 2 Price], size4.Price AS [Size 4 Price]
11 : FROM [products] prod

 

 

by: MikeWalshPosted on 2006-06-04 at 11:02:08ID: 16827973

ok.. so we are a little closer now...

Can you run this query directly in Query Analyzer and look at the results. you may see a little more. What are the data types of all of the columns being used in that query?

 

by: mel150Posted on 2006-06-04 at 11:06:55ID: 16827988

I have a confession to make- I don't know what query analyzer is- but I will look it up and do so now.

 

by: MikeWalshPosted on 2006-06-04 at 11:09:00ID: 16827999

query analyzer is a SQL Server tool.. It should definitely be on whatever machine your database is on.. Depending on if you are using MSDE or a full release..

Start --> Programs --> Microsoft SQL Server --> SQL Query Analyzer

or Start --> Run --> type "isqlw" and hit enter.

My guess is your Product ID and ID columns are different data types in the Products and Sizes table.. Can you find that information easier than query analyzer?

 

by: mel150Posted on 2006-06-04 at 11:14:27ID: 16828021

OH! Wait- I do know- and ok, I just ran it. I got this error: Error converting data type varchar to float.  
In products:
Name            Data Type   Size
id                 int               4        
name            varchar       256      
description     text            16

In sizes:
Name          Data Type      Size
id                 int                4      
code            varchar          50          
size2_price   float              8    
size4_price   float              8  

 

by: MikeWalshPosted on 2006-06-04 at 11:19:52ID: 16828037

ok.. Code is what we wnat to use .. what is stored in "code" is it "size2" or "2" or "size2_price"?

whatever it is you need to use it here:

SELECT 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' -- replace size2 with whatever is in the code field

INNER JOIN [sizes] size4
  ON prod.ID = size4.Product_ID
  AND size4.code = 'Size4' -- replace size4 with whatever is in the code field

 

by: mel150Posted on 2006-06-04 at 12:28:15ID: 16828277

Here is another mistake I have been making, sorry, Mike:

I should have had this:

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

Instead of this:

<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_prices" startrow=1 maxrows=1>$#price#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td></tr>


So I used your query above, now I get Variable NAME is undefined. If I do this, adding prod.name:

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' -- replace size2 with whatever is in the code field

INNER JOIN [sizes] size4
  ON prod.ID = size4.Product_ID
  AND size4.code = 'Size4' -- replace size4 with whatever is in the code field

Then I get the name and description, but this error:

Variable SIZE2_PRICE is undefined.

 

by: MikeWalshPosted on 2006-06-04 at 12:30:37ID: 16828290

that is a problem with application code it sounds like...

try my query in query analyzer and see if it gives you waht you want.

 

by: mel150Posted on 2006-06-04 at 12:37:12ID: 16828317

Why yes, it does!! Thanks, Mike, now I will open a ticket in Coldfusion if I can't figure this out on my own. You're the best!!

 

by: MikeWalshPosted on 2006-06-04 at 12:39:55ID: 16828330

thanks no problem.. I will look through the code that I see above when I come back tonight and see if  Ican help more.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...