MySql Count() Multiple Columns

[BioBob]

Hi!

 

I have a query that I want to count the total number of matches in each row.

 

DB example

 

recipes

  Cereal  recipe_id INT

  Hamburger recipe_id INT

  CHow Mein recipe_id INT

 

ingredients

  recipe_id  (joins to recipe_id in recipes)

  lettuce      ENUM 'y','n'

  tomato      ENUM 'y','n'

  onion        ENUM  'y','n'

  ketchup    ENUM  'y','n'

  mustard    ENUM 'y','n'

 

I've been trying to use SUM, COUNT, HAVING, GROUP BY and every other function I can think of to get this to work.  None of my queries work right so Im great having a brand new query

 

SELECT recipe_id, COUNT(count what?) FROM ingredients WHERE (I build a list elsewhere  ) onion = 'y' OR ketchup = 'y'

 

Then COUNT the number of fields that match.  So if I have onion and ketchup I can make a new results column for COUNT and it gets set to 2, or like if I just have onion match i get a count of 1.  Does that make sense? 

 

result[0] recipe_id value: 43 matches value 2 result[1] recipe_id value 51 value 1

 

Database wasnt set up to use anything other than matches like that so just wondiering how I can accomplish this...

[corbin]

Perhaps I'm having a slow moment, but can you explain again what results you're trying to get?

 

 

Also, if I understand your schema correctly, it would be better to normalize it more.

[BioBob]

Results Im trying to get is recipe_id, TotalMatches...

 

Schema is modified a bit to protect information of my client so Im not really using "recipes"...  Wasnt my design, I was hired on after it was built...