Distinct one column and display other columns below

Question

I am sure I am looking at this in the wrong way.

I would like to hold a restaurant menu in a database and display it. For example, I have created a table similar to the one below:

Columns:

Food_Item | Food_Description | Food_Price | Food_CAT.

Data:

Salad | Refreshing Salad | 5.25 | Starters

Prawn Cocktail | Lovely Prawns | 4.75 | Starters

Tomato Soup | Cream of tomatoe | 4.50 | Soups

Steak | Lovely Rump Steak | 10.95 | Mains

Fish & Chips | Classc dish | 8.75 | Mains

What I am trying to achieve is a menu layout, where the food_CAT acts as the header and then the different dishes are presented below, for example:

Starters
Salad - 5.25

Prawn Cocktail - 4.75

Soups
Tomato Soup - 4.50

Mains
Steak - 10.95
Fish & Chips

etc..

Is there an easy way to do this, so that I don't have the food_cat header above each dish, only over the first one? I thought I could use DISTINCT, however from reading other posts, I understand it's near impossible to DISTINCT one column.

Hope somebody can help.

Regards, Oliver

Answer 1

The way to do this is in the display, not in the query.

Include Food_CAT in the SELECT statement for the query. It will be included in each record retrieved.

Use ORDER BY to sort the items first by Food_CAT and then by whatever column you want, probably Food_Item or Food_Description.

Then, in whatever method you choose to display the results, you iterate through the query, adding a row displaying only Food_CAT each time the value of that field changes. After that header record is displayed, you display the records which match that Food_CAT, and so on.

Answer 2

I'm under the impression that you're asking how to do this in a SELECT statement, not with cursors. If that's not the case, there's a better answer using cursors that can eliminate some of the flotsam in my answer. There are also reporting-tool-specific answers that are far better than both of these (but you don't mention use of a reporting tool).

The key is that you have two data sets you want to intermingle:

• The list of headers
• The item and the price

First step to combine them is to make the data sets match, so you'll want to combine the item and price into a single string. The next is to include the data you want to sort by, which is food_cat, and something that will sort headers to the top of a food_cat, and the item/price string below that. What you end up with looks something like:

select distinct food_cat as display, 
 food_cat, 
 1 as sort_order
from temp_food

union

select food_item + ' - ' + cast (food_price as varchar), 
 food_cat, 
 2
from temp_food

order by food_cat, sort_order

Of course, you have extra columns in this data set you don't want displayed, but that's something you'll have to deal with on another level.

Good luck,

Terry.

Answer 3

SQL isn't very good at mixing and matching different kinds of rows in a single resultset i.e. it would take an overly-complex query to return data in a single resultset like this:

Starters | n/a
Salad | 5.25
Prawn Cocktail | 4.75
Soups | n/a
Tomato Soup | 4.50
Mains | n/a
Steak | 10.95
Fish & Chips | 8.75

You'd be better off using a JOIN to link the data horizontally, and then do a little processing in your client app to display the data in a menu structure.

SELECT Food_CAT, Food_Item, Food_Price FROM xx ORDER BY Food_CAT

Then in the client app, loop through the resultset something like this:

$foodtype = '' // a variable to store which category of food you're currently on
FOR EACH row in myresults:
  IF myresults.Food_CAT <> $foodtype // another item in the existing category
    PRINT myresults.Food_Item + myresults.Food_Price
  ELSE // new category
    $foodtype = myresults.Food_CAT
    PRINT blankline
    PRINT myresults.Food_CAT
    PRINT myresults.Food_Item + myresults.Food_Price