Results 1 to 16 of 16

Thread: [RESOLVED] Help with a query

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] Help with a query

    I can't state this problem succinctly enough to put it into an search engine and find a solution so I am posting here because you guys let me ramble on
    We have a table called Jobs and a table called Trips in a 1:many relationship. Trips.relJobControl = Jobs.Control.
    If the status of *all* the Trips is 'CP' the status of the Job should also be 'CP'. (CP = completed).
    So I want to find bad data, where the Job is not CP but every one of its Trips is CP.
    I'm thinking I need to compare two counts against the Trips table and see if the count of all its rows (that are Trips.relJobControl = Jobs.Control) are the same as the count of all its CP rows, where Jobs.Status is not CP.
    Easy enough to say in English, but not in SQL (well, not for me).
    Version is SQL 12.0.
    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    Tested in SQLite
    Code:
    SELECT 
    T1.Description, 
    T2.CountTripsTotal,
    T3.CountTripsCP
    FROM 
    tblJobs AS T1
    LEFT JOIN 
    (SELECT Job_ID, COUNT(ID) AS CountTripsTotal FROM tblTrips GROUP BY Job_ID) AS T2
    ON
    T1.ID=T2.Job_ID 
    LEFT JOIN 
    (SELECT Job_ID, COUNT(ID) AS CountTripsCP FROM tblTrips WHERE Status='cp' GROUP BY Job_ID) AS T3
    ON 
    T1.ID=T3.Job_id 
    WHERE 
    T1.Status='ncp' AND T2.CountTripsTotal=T3.CountTripsCP
    I used this as Sample Data:
    Code:
    CREATE TABLE IF NOT EXISTS "tblJobs" (
        "ID"    INTEGER,
        "Description"    TEXT,
        "Status"    TEXT
    );
    CREATE TABLE IF NOT EXISTS "tblTrips" (
        "ID"    INTEGER,
        "Description"    TEXT,
        "Status"    TEXT,
        "Job_ID"    INTEGER
    );
    INSERT INTO "tblJobs" ("ID","Description","Status") VALUES (1,'Paint the House','ncp');
    INSERT INTO "tblJobs" ("ID","Description","Status") VALUES (2,'Sue the Boss','cp');
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (1,'Aquire Brushes','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (2,'Aquire Paint','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (3,'Apply primer','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (4,'Let it dry','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (5,'Apply paint','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (6,'Brag in the Neighborhood','cp',1);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (7,'File complain','cp',2);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (8,'Attend court','cp',2);
    INSERT INTO "tblTrips" ("ID","Description","Status","Job_ID") VALUES (9,'Count the money','cp',2);
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Help with a query

    Seems it would be something like this:

    Code:
    Select *
         From Jobs
         Where Jobs.Status='CP'
          and Exists(Select * From Trips Where Trips.JobControl=Jobs.Control
                     and Trips.Status<>'CP')
    or the reverse

    Code:
    Select *
         From Jobs
         Where Jobs.Status<>'CP'
          and Not Exists(Select * From Trips Where Trips.JobControl=Jobs.Control
                     and Trips.Status<>'CP')
    My preference would be to make a view on TRIPS that has a bunch of count columns for total trips, CP trips, non-CP trips and what not (with GROUP BY Trips.JobControl)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    szl,
    second SQL, last Line should read ... Trips.Status='CP'

    EDIT: Forget it.
    I missed the NOT Exists
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Help with a query

    @zvoni - count the money, lol!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    Quote Originally Posted by szlamany View Post
    @zvoni - count the money, lol!
    couldn't resist...

    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    Quote Originally Posted by szlamany View Post
    My preference would be to make a view on TRIPS that has a bunch of count columns for total trips, CP trips, non-CP trips and what not (with GROUP BY Trips.JobControl)
    I always wondered about that: Is it because a VIEW is pretty much a precompiled SELECT-Statement?
    Is there a difference in Performance (Proper Indices on relevant columns provided)?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Help with a query

    Quote Originally Posted by Zvoni View Post
    I always wondered about that: Is it because a VIEW is pretty much a precompiled SELECT-Statement?
    Is there a difference in Performance (Proper Indices on relevant columns provided)?
    It was MMock that floated the business requirement of knowing the count of trips and those in CP status.

    With that said, building that DB object once and testing it robustly is a standard programming technique (in the VB world you would make a function to perform this type of "check").

    Now it can be used in this query and in future queries.

    If you are going to do a GROUP BY on some KEY field, like JobControl, it does not cost any extra time to add more aggregate functions. You can do a MAX(on some date) or a MIN(on some date), SUM(case this and that condition THEN 1 ELSE 0) to your hearts desire and those "extra" CPU clicks to make those fields work is minimal. Think outside the box and add as many fields as you can.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Help with a query

    Thanks to you both! I tweaked and/or slightly modified both queries so I could run them and it's nice that I am getting the same results from each!

    Code:
    Select *
         From Jobs
         Where Jobs.Status<>'CP'
          and Not Exists(Select * From Trips Where Trips.relJobControl=Jobs.Control and Trips.TripStatus<>'CP')
    	  and Exists (Select * From Trips Where Trips.relJobControl=Jobs.Control)
    order by Jobs.Control desc
    Code:
    SELECT 
    T1.SONumber, Control, T1.Status,
    T2.CountTripsTotal,
    T3.CountTripsCP
    FROM 
    Jobs AS T1
    LEFT JOIN 
    (SELECT relJobControl, COUNT(Control) AS CountTripsTotal FROM Trips GROUP BY relJobControl) AS T2
    ON
    T1.Control=T2.relJobControl 
    LEFT JOIN 
    (SELECT relJobControl, COUNT(Control) AS CountTripsCP FROM Trips WHERE TripStatus='cp' GROUP BY relJobControl) AS T3
    ON 
    T1.Control=T3.relJobControl 
    WHERE 
    T1.Status !='cp' AND T2.CountTripsTotal=T3.CountTripsCP
    order by t1.Control desc
    Thanks for your help!
    Marlene
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Help with a query

    @zvoni - looks like you've helped me in the past; I tried to add to your reputation but I have to "spread it around" first!
    @szlamany - you've helped me tons in the past but I was able to add to your reputation again :-).
    P.S. I did Steve's first so not sure how much more spreading I need to do...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    Quote Originally Posted by MMock View Post
    @zvoni - looks like you've helped me in the past; I tried to add to your reputation but I have to "spread it around" first!
    Don't worry. :-)

    But i agree with steve's idea of creating a view on the Trips, if you need to run such a "check" regularly.
    Would go along the lines of (untested)
    Code:
     
    CREATE VIEW 'vwTrips' AS
    SELECT 
    relJobControl,
    COUNT(Control) AS TripsTotal,
    SUM(IIF(TripStatus='cp', 1, 0)) AS TripsCP,
    SUM(IIF(TripStatus<>'cp', 1, 0)) AS TripsOpen
    FROM Trips 
    GROUP BY 
    relJobControl
    Then it's just a (untested)
    SELECT
    Jobs.Control
    FROM
    Jobs
    LEFT JOIN vwTrips
    WHERE Jobs.Status<>'cp' AND Jobs.Control=vwTrips.relJobControl AND vwTrips.TripsTotal=vwTrips.TripsCP

    Just noticed that you could shorten my 2 LEFT JOINS to one by using SUM(IIF)-Method

    EDIT: it could even work for an UPDATE (untested)
    Code:
    UPDATE Jobs 
    SET Status='cp' 
    WHERE  Control IN 
    (SELECT 
    Jobs.Control 
    FROM 
    Jobs
    INNER JOIN vwTrips   //!!!! Changed to an INNER JOIN to avoid catching Jobs that don't have Trips assigned yet
    WHERE Jobs.Status<>'cp' AND Jobs.Control=vwTrips.relJobControl AND vwTrips.TripsTotal=vwTrips.TripsCP)
    you could also check on vwTrips.TripsOpen=0 in the WHERE-Clause instead of equality between Total and Completed
    Last edited by Zvoni; Dec 1st, 2021 at 10:02 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: Help with a query

    Well, I will probably run it "regularly" like once a week and then as I see no new rows being returned, indicating the bug is fixed, maybe just once a month so I think I will pass on the view but thanks for the suggestion. Since it's not being called from my code and doesn't need to hang around, I'd rather not have it created in the db.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Help with a query

    No need for SELECT DISTINCT with the GROUP BY. You want to make sure the GROUP BY is working properly, and DISTINCT would mask that, imo.

    I am very, very against DISTINCT, fwiw...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: [RESOLVED] Help with a query

    Quote Originally Posted by szlamany View Post
    No need for SELECT DISTINCT with the GROUP BY. You want to make sure the GROUP BY is working properly, and DISTINCT would mask that, imo.

    I am very, very against DISTINCT, fwiw...

    Removed the DISTINCT above in post #11
    No idea how that one sneaked in.
    If you have GROUP BY, DISTINCT is not necessary and just adds noise and might mask a bug, as steve pointed out
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  15. #15

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: [RESOLVED] Help with a query

    Yaassss! Avoid DISTINCT at all costs! (But never say "never"!)
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: Help with a query

    Quote Originally Posted by MMock View Post
    Well, I will probably run it "regularly" like once a week and then as I see no new rows being returned, indicating the bug is fixed, maybe just once a month so I think I will pass on the view but thanks for the suggestion. Since it's not being called from my code and doesn't need to hang around, I'd rather not have it created in the db.
    In that case my SQL cleaned up to use one INNER JOIN
    Code:
    SELECT 
    T1.SONumber, T1.Control, T1.Status,
    T2.CountTripsTotal,  //For demonstration only. Could be left out
    T2.CountTripsCP,     //For demonstration only. Could be left out
    T2.CountTripsOpen 
    FROM 
    Jobs AS T1
    INNER JOIN 
    (SELECT 
    relJobControl, 
    COUNT(Control) AS CountTripsTotal,                       //For demonstration only. Could be left out
    SUM(IIF(TripStatus='cp',1,0)) As CountTripsCP,           //For demonstration only. Could be left out
    SUM(IIF(TripStatus<>'cp',1,0)) As CountTripsOpen 
    FROM Trips GROUP BY relJobControl) AS T2
    ON
    T1.Control=T2.relJobControl 
    WHERE 
    T1.Status !='cp' AND T2.CountTripsOpen=0 
    order by t1.Control desc
    Last edited by Zvoni; Dec 1st, 2021 at 10:33 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width