SQL Select Problem

Raskolnikov

Hoping someone can help me out here . . .

I have a table that contains a number of records. It has two fields, "Starter" and "Main Course". Typical data would look like this. . .

Starter¦Main
Soup, Beef
Soup, Chicken
Salad, Pork
Soup, Beef
Salad, Chicken
Soup, Beef
Salad, Pork
etc . . .

I need to try and select all similar pairs that occur in the table more than once. For example, in the above table, I would be looking to select Soup and Beef and Salad and Pork as they occur more than twice.

Anyone help?

stipey

My first tip would be to search the web - because it certainly doesn't strike me as a query somebody would still need assistance with after a quick trawl through google.

The answer is more likely to stick in your mind if you discover it yourself through google or MSDN.

I'll point you in the direction however - the key words to search for are "count", "group by" and (if you want to find instances where a combination appears more than once "having". It would help if you were to add +sql to your search to ensure you only get hits relating to SQL.

fasty

SELECT DISTINCT Starter, Main WHERE Count(Starter + Main) > 1

mathie

SELECT starter, main, COUNT(*) AS Expr1
FROM yourtable
GROUP BY starter, main
HAVING (COUNT(*) > 1)

Raskolnikov

That works a peach Mathie, the problem I was having was that I was trying to put the (COUNT(*) > 1) into the WHERE part of my SQL query, that of course gave me an error.