MySQL multiple table count question

How to structure properly?

[bcolflesh]

Can't seem to do this completely in SQL - probably very easy:

2 tables:

TABLE stuff (
ID int(11) NOT NULL auto_increment,
stuff_status varchar(20) NOT NULL default 'Open',
PRIMARY KEY (ID)
) TYPE=MyISAM;

TABLE related (
rel_ticket_id int(11) NOT NULL default '0',
req_visit varchar(10) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=MyISAM;

stuff.ID equals related.rel_ticket_id

I need to return a rowset where stuff.stuff_status = 'finished' AND stuff.ID equals related.rel_ticket_id AND req_visit = 'yes'

AND where the total records that fit the above criteria from TABLE related are equal to a numeric value.

Ex: Stuff that is 'finished' after 1 req_visit
Stuff that is 'finished' after 2 req_visits
Stuff that is 'finished' after 3+ req_visits

Someone point me in the right direction!

Regards,
Brent

[bonanza]

If I understand correctly, you want to get counts of your results and then use HAVING to specify the counts to show, something like this:

select stuff.id, count(stuff.id)
from stuff, related
where stuff.stuff_status = 'finished' AND
stuff.ID = related.rel_ticket_id AND
req_visit = 'yes'
group by stuff.id
having count(stuff.id) = 2

That'll show the counts of the stuff IDs that have a row in the related table (with just yes's) and return only the ones with two matches.

[bcolflesh]

I think that is the help I need - thanks bonanza!

Regards,
Brent