#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Sausalito,ca
    Posts
    3
    Rep Power
    0

    Angry 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
    Last edited by victorpendleton; February 6th, 2003 at 09:27 PM.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Sausalito,ca
    Posts
    3
    Rep Power
    0

    Question


    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select companyInfo.*,
     (select count(*) from jobListings
      where companyInfo.companyID = jobListings.companyID)
     from companyInfo
    where companyInfo.siteID = 1
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Sausalito,ca
    Posts
    3
    Rep Power
    0

    Thumbs up


    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


IMN logo majestic logo threadwatch logo seochat tools logo