?
Solved

Select a distinct column, and non-distinct columns

Posted on 2006-05-29
58
Medium Priority
?
1,628 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:mel150
  • 25
  • 24
  • 6
  • +3
58 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 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
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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?
0
 

Author Comment

by:mel150
ID: 16785787
but it won't let me select description, because it is a datatype text.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16785798
show us what you want and we can help
0
 

Author Comment

by:mel150
ID: 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!


0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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?
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16785853
The reason I ask, is because this looks different than the example query in your original query.. So I am little confused.
0
 
LVL 13

Expert Comment

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

Author Comment

by:mel150
ID: 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!!
0
 

Author Comment

by:mel150
ID: 16785868
<offtopic>LOL- I can't keep up with you guys! It does sound good, doesn't it? </offtopic>
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16785874
It sounds great..

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

Author Comment

by:mel150
ID: 16785875
There are always two prices, for two servings and four servings.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 16785895
Thanks very much-I will try this tonight!
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 16785916
OK-thanks! Not insulting at all, I really am new at this. :)
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 
LVL 11

Expert Comment

by:anyoneis
ID: 16786054
How about:

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


David
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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??
0
 

Author Comment

by:mel150
ID: 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!
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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
0
 

Author Comment

by:mel150
ID: 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;.  
 
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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
0
 

Author Comment

by:mel150
ID: 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?
0
 

Author Comment

by:mel150
ID: 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,
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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
0
 

Author Comment

by:mel150
ID: 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...

0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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?
0
 

Author Comment

by:mel150
ID: 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!!
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 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
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 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


0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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
0
 

Author Comment

by:mel150
ID: 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. :)

0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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 :)
0
 

Author Comment

by:mel150
ID: 16799339
I did try- I got an error, and made some changes and got another error...  that's when I decided to unscramble. :)
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16799456
lol.. okay.. if you need help, report your error, it is probably something to do with the table names/column names .
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 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
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 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...
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 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
 
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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'
0
 

Author Comment

by:mel150
ID: 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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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'
0
 

Author Comment

by:mel150
ID: 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

 
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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?
0
 

Author Comment

by:mel150
ID: 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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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?
0
 

Author Comment

by:mel150
ID: 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  
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 2000 total points
ID: 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
0
 

Author Comment

by:mel150
ID: 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.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0
 

Author Comment

by:mel150
ID: 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!!
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question