Select a distinct column, and non-distinct columns

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

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?

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

show us what you want and we can help

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!

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?

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

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

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!!

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

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.

It sounds great..

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

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

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.

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.

Thanks very much-I will try this tonight!

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.

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

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.

How about:

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


David

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??

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!

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

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;.  
 

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

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?

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,

>>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

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...

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?

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!!

/*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

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.

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

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.

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

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. :)

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 :)

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

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

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

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.

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...

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.

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
 

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'

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.

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'

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

 

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?

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

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?

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  

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.

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.

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!!

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