-
Nov 30th, 2021, 02:04 PM
#1
Thread Starter
PowerPoster
[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.
-
Dec 1st, 2021, 03:36 AM
#2
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
-
Dec 1st, 2021, 05:30 AM
#3
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)
-
Dec 1st, 2021, 05:41 AM
#4
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
-
Dec 1st, 2021, 06:03 AM
#5
Re: Help with a query
@zvoni - count the money, lol!
-
Dec 1st, 2021, 06:16 AM
#6
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
-
Dec 1st, 2021, 06:59 AM
#7
Re: Help with a query
Originally Posted by szlamany
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
-
Dec 1st, 2021, 09:14 AM
#8
Re: Help with a query
Originally Posted by Zvoni
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.
-
Dec 1st, 2021, 09:17 AM
#9
Thread Starter
PowerPoster
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.
-
Dec 1st, 2021, 09:21 AM
#10
Thread Starter
PowerPoster
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.
-
Dec 1st, 2021, 09:49 AM
#11
Re: Help with a query
Originally Posted by MMock
@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
-
Dec 1st, 2021, 09:54 AM
#12
Thread Starter
PowerPoster
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.
-
Dec 1st, 2021, 09:59 AM
#13
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...
-
Dec 1st, 2021, 10:03 AM
#14
Re: [RESOLVED] Help with a query
Originally Posted by szlamany
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
-
Dec 1st, 2021, 10:07 AM
#15
Thread Starter
PowerPoster
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.
-
Dec 1st, 2021, 10:30 AM
#16
Re: Help with a query
Originally Posted by MMock
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|