Query Help

[julisana]

Hi Guys!

 

This is probably something really simple, but I'm having some problems figuring out how best to write a particular query.

 

I've got two tables: 1 that has entries for when a certain process starts and stops for each company in our database (`stage`), and one that has all the queued up items that process has to take care of for each company (`queue` - each item is it's own row).

 

Right now the query tells me how many companies have been processing their queue since time X (let's say midnight)...

 

SELECT company_id, stage, time
FROM `stage`
WHERE time < %s AND stage != 'inactive'"

(the %s will contain the unix timestamp for Time X on the current day).

 

...but I'd like it also to tell me how many items it has left to process for each of those companies.

 

I thought about just adding a count() to the query, but that's not exactly going to return the results I want, since I'm not sure how to specify that I want it to pull the # of items for each individual company specified in the query above (found in table #2, `queue`).

 

`queue` contains the company_id value referenced in the stage table, so I'm assuming I'd need to do something with that to make sure I only got items pertaining to that company, but I"m not sure how. 

 

I don't know if I need to use a Join or a subquery or both or what.  Can someone point me in the right direction for how to do this all in one nice neat statement?

 

Thanks!

[Muddy_Funster]

you'll need a join.  Post us your table info and we can help a bit more.

[julisana]

Here are the fields in the two tables -

Tbl 1:

company_id, stage, time

 

tbl 2:

queue_id, company_id, data, random

 

Tbl 1 has 1 entry for each company in our database.  Each of those entries gets changed depending on whether the process is running or not.  (If it's not running, the value of stage is "inactive", if it is running, the value of stage could be "running" or "finishing"). 

Tbl 2 can have anywhere from 1 to n entries for any company in our database.

 

Does this information help?

[Muddy_Funster]

sure does, have a look at this:

SELECT company_id, stage, time, COUNT(queue_id) Remaining_Processes FROM
Tbl1 RIGHT JOIN Tbl2 ON
(Tbl1.company_id = Tbl2.company_id)
WHERE stage <> "inactive"
GROUP BY company_id

I don't have time to test it, but it should let you see how to get what your looking for.

[julisana]

That seems to do what I need it to do, thank you!!!

[julisana]

Actually, no it doesn't.  It counts the total number of items in the queue, not the total number of items for each company that's in table 1.

 

 

Ideas?

[fenway]

With that GROUP BY, it's per company -- I'm confused.

[julisana]

You're right, of course.

 

To get everything to work right, I had to put the table names in front of the fields since there was more than one table involved.  I got everything working, except for the fact that I specified tbl1 instead of tbl2 when defining the table in the GROUP BY clause.

 

Durrr to me!

 

Thanks for all the help guys, I really appreciate it!