I am using Firebird 2.5.8 and Delphi 10.2.3 and I want to fill a DBGrid with a query:
SELECT c.ID, l.ID,
(
SELECT COUNT(pl.ID)
FROM Tbl_ProtocolLicense AS pl
WHERE (pl.ReferenceId=l.ID)
) AS ReferenceCount
FROM Tbl_License AS l, tbl_client AS c
WHERE l.ClientId=c.Id;
How I can add a value ( ReferenceCount > 0 ) as boolean or (0/1) to that query?
Why even use a correlated query that would be re-calculated again and again for every row ?
The first query does not actually work. Was too hasty.
SELECT
c.ID,
l.ID,
IIF( r.CNT > 0, 1, 0 )
FROM Tbl_License AS l
JOIN tbl_client AS c ON l.ClientId=c.Id
JOIN (
SELECT COUNT(*) as CNT, ReferenceId as ID
FROM Tbl_ProtocolLicense
GROUP BY 2
) as r ON r.ID = l.ID
Note: this assumes that Tbl_ProtocolLicense.ID column is never NULL.
UPD. I gave a bit of lecture about COUNT and other aggregates at http://stackoverflow.com/a/51159126/976391 - but here I missed it myself.
SELECT COUNT(*) as CNT, ReferenceId as ID
FROM Tbl_ProtocolLicense
GROUP BY 2
Do run the query and see the result. Notice anything fishy?
This query only returns rows that do exist, not those that do not exist.
The intermediate grouping query would not have a single row, where count=0 ! And thus the whole Inner Join based query would not have them too!
What should we do is using Outer Join, that lets row exist even when there is no matching row in another table. Read: https://en.wikipedia.org/wiki/Join_(SQL)
SELECT
c.ID,
l.ID,
IIF( r.CNT is not NULL, 1, 0 )
FROM Tbl_License AS l
JOIN tbl_client AS c ON l.ClientId=c.Id
LEFT JOIN (
SELECT COUNT(*) as CNT, ReferenceId as ID
FROM Tbl_ProtocolLicense
GROUP BY 2
) as r ON r.ID = l.ID
Compare the output with the first query and see the difference.
UPD 2. However even that was not good enough, probably. Problem here is that "you say you want the things you do not actually want".
You demand Firebird to COUNT ALL the rows, when you really DO NOT care about the count. All you care is "if there is at least one row or none at all". If there is one row - you do not care if there would be 10 or 100 or 1000 more. So actually counting objects when you do not want their count - is an extra work done for nothing.
That is especially wasteful in Interbase/Firebird family, where counting over the table can trigger garbage collection and slow down the work. But it would be true even in pure Delphi - you do not want to loop through ALL the array if you would be satisfied with finding first suiting element of it.
And then we can move back to the correlated sub-query.
SELECT
c.ID,
l.ID,
IIF( EXISTS (
SELECT * FROM Tbl_ProtocolLicense AS pl
WHERE pl.ReferenceId=l.ID
), 1, 0 )
FROM Tbl_License AS l, tbl_client AS c
WHERE l.ClientId=c.Id;
Which is worse? Who knows. Depending on the real data and real tables/indexes - there can be case when one or another approach would be faster. Human would not notice the difference on small data. It is the question on "scaling up" to thousands and millions of real data, where the difference would show.
UPD 3. Can we have best of the both approaches? I hope we can. The trick is - asking exactly what we need and not any more than that. Can we ask Firebird to list all the IDs which we have in the table without actually counting them? Yes, there is.
SELECT DISTINCT ReferenceId FROM Tbl_ProtocolLicense
Run the query and see the result!
Notice, it still would NOT list the IDs that are not in the table. Obvious? Well, I missed it in my first approach and then two persons upvoting me missed too. Stupid errors are hardest to spot, as you can not believe such stupidity.
So, now we have to plug it instead of "counting" query of the 2nd attempt.
SELECT
c.ID,
l.ID,
IIF( r.ReferenceId is NULL, 0, 1 )
FROM Tbl_License AS l
JOIN tbl_client AS c ON l.ClientId=c.Id
LEFT JOIN (
SELECT DISTINCT ReferenceId
FROM Tbl_ProtocolLicense
) as r ON r.ReferenceId = l.ID
UPD. 4 One last trick. If I am correct, this query would have exactly the same result as above, without using IIF/CASE. Try it and compare. If the results are same - then try to understand why and how it works and which extra assumptions about data it requires.
SELECT
c.ID,
l.ID,
COUNT( r.ReferenceId )
FROM Tbl_License AS l
JOIN tbl_client AS c ON l.ClientId=c.Id
LEFT JOIN (
SELECT DISTINCT ReferenceId
FROM Tbl_ProtocolLicense
) as r ON r.ReferenceId = l.ID
GROUP BY c.ID, l.ID
This query is not better than Upd.3, it is just a quest to think about and then to understand SQL better.
Now do some work to actually check and compare the results, because blindly trusting unknown person on the internet is not good. Even if that person is not malicious, he can make stupid mistakes too.
Whatever you peek from Internet forums, that is only "example" and "idea demonstration", and it is always your responsibility to understand and check that example. And maybe to reject it.
To read and to understand:
Additionally it would really be useful for you to read some good book on general SQL, like Martin Gruber's ones
Select ..... MaxValue( MinValue( ReferenceCount, 1), 0) ....:-P