all records for one table , and a count of records based on ID from another

Can anbody help me with the sql for returning all the records in one table, and in each row, a count of how many records with the key id in another table...

english(ish) query:

select * from ci.companyInfo and count(jl.jobListingInvoicespaid) as jobsCount where siteID=1 and ci.companyID = jl.companyID


thanks,

MG

SELECT COUNT(table1.*), COUNT(table2.id) as jobsCount
FROM table1
INNER JOIN table2
ON table1.companyID = table2.companyID
AND table1.siteID = 1
GROUP BY (table2.id)

Forgive me if my syntax is off.
...

Your request is somewhat unclear but I think this may be what you are asking for. If so, you should create and index for table on the companyID and the siteID and an index on the companyID in table2.

Thanks for the attention... I realize my post was not clear... Let me modify what you have sent as if to better describe what I need.

SELECT companyInfo.*, COUNT(jobListings.jobListingInvoicePaid) as jobsCount
FROM companyInfo
INNER JOIN jobListings
ON companyInfo.companyID = jobListings.companyID
AND companyInfo.siteID = 1
GROUP BY (jobListings.companyID)

this returns the dreaded - "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

If I remove the companyInfo.* from the select line, it returns the counts of the jobs for each company, that is what I need, but I am trying to do is link the counts of how many jobs a company has. I have a companies table and a jobs table and each job is keyed to the companies ID. So what I hope to get is something like this:

co.coName co.coId jobs(count)joblistings
Microsoft x9re4w 5
Sun 49gti6 27
Mookey

Sweet...

You know, that is about how I thought it would work but I didn't even try to explore nesting of select statements.

This is my first time using this resource (devshed). It works exactly as it should, you post a question, you get the help you need.

Thanks a lot!!! Everyone pat themselves on the back...


MG