Need help checking conditions from another table

[DWilliams]

Long story short I'm making a system for work that allows users to enter a list of businesses and then generate envelopes to be printed with the proper address. I could go on and on but my problem boils down to a query I need to write. To start, here's my database design:

 

mysql_query("CREATE TABLE business(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(75),
notes TEXT,
cat_id INT,
status VARCHAR(25))") or die(mysql_error());

mysql_query("CREATE TABLE flyer(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
bus_id INT,
addr_id INT,
datesent VARCHAR(10),
status VARCHAR(25))") or die(mysql_error());

mysql_query("CREATE TABLE address(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
bus_id INT,
address VARCHAR(50),
address2 VARCHAR(50),
attn VARCHAR(50),
city VARCHAR(25),
state VARCHAR(2),
zip VARCHAR(15),
status VARCHAR(25))") or die(mysql_error());

mysql_query("CREATE TABLE category(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(25))") or die(mysql_error());

 

Now, I need to create a query to determine how many flyers are scheduled for each "category".  The problem is, the flyer table and category tables don't have a direct link. They are only connected to one another via the business table, which holds a cat_id to represent what category it falls under. The flyer table is obviously only linked to the business table via the entries it holds which contain a bus_id.

 

My plan so far to see how many flyers are scheduled in each category (keeping in mind that a scheduled flyer is indicated by the status column in the flyer table set to SCHEDULED) is to loop through each category and query for each one. My problem is that I'm not very adept at doing any sort of cross-table queries.

 

I can do something like this to get the total number of flyers per category:

SELECT COUNT(*) as count FROM business WHERE category=$myCategoryLoopingRowIdHere

 

But when it comes to restricting the query to only grab businesses with scheduled flyers I get lost. I need some sort of conditional here in addition to what I already have that states "only retrieve businesses that have an entry in the flyer table with a status of "SCHEDULED".

 

Thanks in advance for any help. I'm sorry if I didn't provide enough information, I have database on the brain which renders me incapable of  basic human communication for a good three hours.

[o3d]

Well I think this query might do the trick (remember to remove comments):

 

select count(c.id) as cnt
from business a join category b on b.id = a.cat_id and b.name = 'SCHEDULED' /*brings up all businesses with scheduled categories*/
join flyer c on c.bus_id = a.id /*limit results to flyers linked to businesses*/

[DWilliams]

Well I think this query might do the trick (remember to remove comments):

 

select count(c.id) as cnt
from business a join category b on b.id = a.cat_id and b.name = 'SCHEDULED' /*brings up all businesses with scheduled categories*/
join flyer c on c.bus_id = a.id /*limit results to flyers linked to businesses*/

 

This didn't quite work, but that's my fault for not explaining my database design better. "categories" aren't scheduled, "flyers" are.

 

Your code did get me on the right track, however. After some tinkering I came up with this code to see how many scheduled flyers there are per category:

 

$catQuery = mysql_query('SELECT id, name FROM category') or die(mysql_error());
while($catRow = mysql_fetch_array($catQuery))
{ 
  $countQuery = mysql_query("SELECT COUNT(flyer.id) as count
                             FROM business JOIN category ON business.cat_id = category.id
                             JOIN flyer ON flyer.bus_id = business.id AND flyer.status = 'SCHEDULED' 
                             WHERE business.cat_id={$catRow['id']}") or die(mysql_error());
  $countRow = mysql_fetch_assoc($countQuery);

  $id_to_cat[$catRow['id']] = $catRow['name'];
  $id_to_count[$catRow['id']] = $countRow['count'];
}

at which point I can use the associative arrays to look up the counts for later displaying

 

I haven't got to test it extensively thus far but my rudimentary testing says it works