Distinct combined with asterisk?

Ah.... good question. It won't allow me to pull the other parameter distinctly, because it is datatype text. Can I make that column a different datatype? It is quite long, as it is a description of a product. Thanks!!

Hi- I guess I could change it to varchar? I will try that.

Hi- Guess what? Not sure how to do that :)

Would it be:

Currentdb.Execute "ALTER TABLE products ALTER COLUMN description varchar"

?

I dont think it has anything to do w/ column type.  Pretty sure you cannot do what you are trying to do.

Sorry about that, I was not clear about what I meant. That happens often. :)

I meant that I understand what you mean about not being able to pull only one column as distinct. In that case, I have 2 columns I am trying to pull, name and description. Column 'name' is varchar, column 'description' is text. If I try to say
SELECT Distinct name, description

I get an error that says datatype text cannot be distinct.

So, as much as it makes me nervous, I wondered if chaging the datatype on the column description to varcahr would solve my problem.

True. Text is a weird one. You can change it to VARCHAR() and then use distinct on it....

OR you may be able to do this:

SELECT DISTINCT(CONVERT(VARCHAR(100),{myTextColumn}))
FROM {MyTable}

In this example, replace {MyTextColumn} with your column name and MyTable with your table name

Then if you want to do a distinct on more than one column, try this:

SELECT DISTINCT(CONVERT(VARCHAR(1000),{myTextColumn})) , CONVERT(VARCHAR(1000), {myOtherColumn}))
FROM {MyTable}


M@

Wow! Interesting! Thanks, ExpertAdmin, I'll try that.

Hi again..here is my query now:
<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT    Distinct products.name , (CONVERT(VARCHAR(1000),products.description)), sizes.size2_price, sizes.size4_price  
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

And then I have:
<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td><td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td>
</tr>

The name is being pulled, but then it says Variable Desciption is undefined..any ideas? Thanks!

Oh...


Sorry, should have been this:

SELECT DISTINCT(CONVERT(VARCHAR(100),{myTextColumn})) AS MyTextColumn
FROM {MyTable}

The way it was did not give it a column name to return the conversion in.

M@

Thanks!! I will try that on my break in a few hours.

Interesting... unless I'm nuts, which is a possibilty, this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT  DISTINCT (CONVERT(VARCHAR(100), products.description)) AS description, (CONVERT(VARCHAR(100), products.name)) AS name, (CONVERT(VARCHAR(100), sizes.size2_price)) AS size2_price, (CONVERT(VARCHAR(100), sizes.size4_price)) AS size4_price  
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

Gives me the same results as this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT     *
    FROM      products INNER JOIN sizes
      ON  products.id=sizes.product_id
      WHERE products.active=1
      ORDER BY products.name
</CFQUERY>

When I am calling:

<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td>
<td><cfoutput query="get_products" startrow=1 maxrows=1>#size2_price#</cfoutput></td><td><cfoutput query="get_products" startrow=1 maxrows=1>#size4_price#</cfoutput></td>
</tr>

In other words, it isn't pulling the columns distinctly. Does that make sense? Thanks!!

Just for clarification, the distinct will be distinct for ALL COLUMNS together. In other words, all of the following are distinct:

X Y Z
X Y Y
Y X Z
Y Y Y
Z Z Z

The fact that there are not two rows with the same identical values in the same columns makes them distinct. Is this consistent with what you are thinking?

M@

hmm.. maybe I'm misunderstanding Distinct after all. What I thought I would get was:

Name of menu item| description of item|price for 2 servings of item| price for 4 servings of item

But what I'm getting is:
First row:
Name of menu item|description of item|price for 2 servings of item|blank column
next row:
Name of same menu item|description of item|blank column|price for 4 servings of item

Which is the same thing I pull if I use this query:

<CFQUERY DATASOURCE="#request.maindsn#" NAME="get_products">
    SELECT     *
    FROM      products INNER JOIN sizes
     ON  products.id=sizes.product_id
     WHERE products.active=1
     ORDER BY products.name
</CFQUERY>

I'm sure I'm missing something- thanks for your patience!

Yea...Distinct just means "Don't repeat the rows if more than one row is identical". Since you apparently only have them listed once, then SELECT * and SELECT DISTINCT * will yield the same list.

Tell me a little more about what you are attempting to do and maybe we can figure it out.

M@

Ok, thanks!- I have two tables. One is products, one is sizes. The relevant columns are:

products: name, description
sizes: size2_price, size 4_price

and the linking columns are: products.id and sizes.product_id

Now, sizes has each product_id in two rows, because there are two sizes for each product. And each row has a price for that size:

product_id    code        price
2060            size_2      5.50
2060            size_4      11.00


Because I want my table to pull the price for each size, I thought I needed to add two columns, and maybe I made a mistake here- I added two columns to the table sizes, size2_price and size4_price.

I want my results to be:

The name (only once), the description (only once) the price for size_2, the price for size_4.

Does that make sense? Do you need more info? Thanks very much for your help!

I see what you are saying.


This will get the results you want, but it is not very good code:

SELECT Product.Name, (SELECT Price FROM Sizes WHERE code = 'size_2') AS size2_price, (SELECT Price FROM Sizes WHERE code = 'size_4') AS size4_price)

I think my main question at this point is: why do you only want to return one row? That is not really the way a relational database is designed to work. Normally you would return multiple rows, then iterate through them in your application to pull the relevant information from each row. Like this:

Product:    My Product

Pricing
 
  QTY        PRICE
     2             5.50
     4           11.00
    ...etc...

In this scenerio, you wouldn't even need to return the product name...it would be in a different query. You would simply pass in the product ID to get the list of prices.

M@

Hmm.. the one row is for aesthetics. What I am really doing is creating a menu, an interactive menu. Part of that query will be where products.active=1. So when a product becomes inactive, it drops from the menu, or conversely if it becomes active, it appears on the menu.

 But I don't want "Meatloaf with Tomato Glaze" to appear in two rows. Make sense?

Yes...but what I think what one of us is missing is this: it seems to me that in your thinking the results of the query is coupled directly to the menu. This would make sense if the menu were (or is) based on XML. But if it is in an application (like ASP or ColdFusion), you can create the menu items in code any way you like.

But that is no big deal. The query with the subselects works, but the problem with it is that you have to know ahead of time what the quantities will be in order to do the subselect properly. This may or may not solve your problem.

Just to help me understand the constraints you are working with...how are you building these menus?

M@

I hope this is the answer you need: I am building them in coldfusion. Let me see if I can simplify: This code below gives me exactly what I want, EXCEPT the prices aren't linked to the product, they are arbitrary because I haven't joined the 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>

<table width="100%" border="1" cellspacing="2" cellpadding="2" align="left">
<tr><td><b>Menu Item</b></td><td><b>2 servings</b></td><td><b>4 servings</b></td></tr>

<tr><td><cfoutput query="get_products" startrow=1 maxrows=1><b>#name#:</b>
</cfoutput>
<cfoutput query="get_products" startrow=1 maxrows=1>#description#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td><td><cfoutput query="get_prices" startrow=1 maxrows=1>$#price#</cfoutput></td></tr>

Then I get:
Menu Item                                              Price for 2                Price for 4
Meatloaf -Meatloaf description                   8.00                         15.50              But those prices are incorrect.

Does that make it clearer or muddier? Thanks!!

OK. Here is how I would go about it:


First, your prices table should look like this:

ProductID
Quantity
Price

So for a single product, you would have more than one row (as you stated):

Product     Qty         Price
------------------------------------------
0001           2          5.50
0001           4          11.00
0002           2          2.45
0002           4          4.90


With this structure, you could do this:


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

<CFOUTPUT QUERY = "get_products">   <!---First, loop through the products list--->
   
   <CFQUERY DATASOURCE="#request.maindsn#" NAME="get_prices">  <!--- For each product, get the list of quantities and prices--->
            SELECT     *
            FROM      sizes
            WHERE Product = '#Product#'
    </CFQUERY>


    <TR>        <!--- Start a new row for each product.--->
           <TD>
                    #Product#              <!---Add the product name --->
           </TD>
          <CFOUTPUT QUERY ="get_prices">   <!---Loop through the prices, adding a cell for each one --->        
                        <TD>#Quantity#</TD>
                        <TD>#Price#</TD>
          </CFOUTPUT>

    </TR>       <!--- Close out this product's row--->    

</CFOUTPUT>


With this setup you can specify as many prices as you need, and you don't have to name your columns to match the entry.

M@

Thanks!! I'll give this a try tonight.