#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0

    mother of all querys


    Hey all,

    I'm trying to do a COUNT and multiple SELECT's in one query if possible:

    Here is my query currently and ill include the COUNT down the bottom as I don't know where to put it.

    Code:
    function find_completed_jobs(){
       global $db;
       $sql  = " SELECT job.id AS jobid, job.contractor_id, contractor.id, contractor.name AS contractor, job.location AS location, job.current_length AS cmeters, job.length AS bore, job.bore_size AS boresize, job.ground_conditions AS ground, job.job_status_id, job_status.Status, job.driller_id, users.id, users.name AS operator";
       $sql .= " FROM job JOIN job_status JOIN contractor JOIN users JOIN job_progress";
       $sql .= " WHERE job.job_status_id = '2'";
       $sql .= " AND job_status.Status = 'Complete'";
       $sql .= " AND job.contractor_id = contractor.ID";
       $sql .= " AND users.id = job.driller_id";
       $sql .= " AND job.driller_id = job_progress.driller_id";
       return find_by_sql($sql);

    I'm trying to add

    Code:
    COUNT(ID) from job_progress AS daystocomplete
    WHERE job_progress.job_id = job.ID
    AND job_progress.driller_id = job.driller_id
    Code:
    my job_progress table is setup like this
    ID       job_id     driller_id      date
    1        4              4                2017-07-03
    2        4              4                2017-07-04
    3        4              4                2017-07-05
    4        4              4                2017-07-06
    5        4              4                2017-07-07
    6        3              5                2017-07-10
    basically I want to count the number of days driller was at job

    Any help would be apprecated
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,952
    Rep Power
    381
    FWIW, I find this easier to read:
    Code:
       
       $sql  = " 
       SELECT j.id jobid
            , j.contractor_id
            , c.id 
            , c.name contractor
            , j.location location
            , j.current_length cmeters
            , j.length bore
            , j.bore_size boresize
            , j.ground_conditions ground
            , j.job_status_id
            , s.Status
            , j.driller_id
            , u.id 
            , u.name operator
         FROM job j
         JOIN job_status s
         
         JOIN contractor c
           ON c.ID = j.contractor_id 
         JOIN users u
           ON u.id = j.driller_id   
         JOIN job_progress p
           ON p.driller_id =  j.driller_id 
        WHERE j.job_status_id = 2
          AND s.Status = 'Complete'"
          ";
    Note that 1) job_status is a cartesian join and 2) at present you have no way of differentiating between user ids and contractor ids in the result set

    The following will tell you how many days a driller was a job (btw, these must be massive boreholes)
    Code:
    SELECT job_id
         , driller_id
         , COUNT(DISTINCT date) days_at_job
      FROM job_progress 
     GROUP
        BY job_id
         , driller_id;
    Last edited by cafelatte; July 22nd, 2017 at 07:46 AM.
  4. #3
  5. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,072
    Rep Power
    4101
    You should be able to just add the count as a sub query. If your result set is small then adding it to the select list should be quick and simple.
    Code:
    SELECT 
        j.id jobid
        , j.contractor_id
        , c.id 
        , c.name contractor
        , j.location location
        , j.current_length cmeters
        , j.length bore
        , j.bore_size boresize
        , j.ground_conditions ground
        , j.job_status_id
        , s.Status
        , j.driller_id
        , u.id 
        , u.name operator
        , (
            SELECT COUNT(job_progress.id) 
            from job_progress
            WHERE 
                job_progress.job_id = j.id
                AND job_progress.driller_id = j.driller_id
        ) as daysToComplete
    FROM job j
    JOIN job_status s ON j.job_status_id = status.id
    JOIN contractor c ON c.ID = j.contractor_id 
    JOIN users u ON u.id = j.driller_id   
    JOIN job_progress p ON p.driller_id =  j.driller_id 
    WHERE 
        s.Status = 'Complete'
    I took a stab in the dark at what your join condition for the job_status table should be.

    If you're pulling a larger result set then it might be better to do a grouped sub-query like cafelatte showed then join to it for the results. Trying both with EXPLAIN in the front would tell you which is better.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,699
    Rep Power
    4288
    1. if you specify job.job_status_id = '2' then you don't really need to join to job_status at all, assuming 2 corresponds to 'Complete'

    2. if you specify job.contractor_id = contractor.ID then you don't need both of them in the SELECT clause... same for job.driller_id and users.id

    Code:
    SELECT job.id AS jobid
         , job.contractor_id
         , contractor.name AS contractor
         , job.location 
         , job.current_length AS cmeters
         , job.length AS bore
         , job.bore_size AS boresize
         , job.ground_conditions AS ground
         , '2' AS job_status_id
         , 'Completed' AS Status
         , job.driller_id
         , users.name AS operator
         , subquery.daystocomplete
      FROM job 
    INNER
      JOIN contractor 
        ON contractor.ID = job.contractor_id
    INNER
      JOIN users 
        ON users.id = job.driller_id
    INNER
      JOIN ( SELECT job_id
                  , driller_id
                  , COUNT(*) AS AS daystocomplete
               FROM job_progress 
             GROUP
                 BY job_id
                  , driller_id ) AS subquery
        ON subquery.job_id = job.id
       AND subquery.driller_id = job.driller_id
     WHERE job.job_status_id = '2'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Posts
    15
    Rep Power
    0
    Sweet, Thanks fella's it works exactly as I wanted.

    Cheers

IMN logo majestic logo threadwatch logo seochat tools logo