SQLite SQL Question

Last time i did anything non trivial (more than a single table fields select) with SQL was about 4 years ago and that relatively simple too, so I was never very knowledgeable and now I’m also very rusty…

I was wondering how I should approach this… I could do it with more than one select, but I would rather do it in one if I could.

I Need to pull data from two tables based on key from another table, but the data from the second table is not just field values from a single record.

Table0

KeyT0 ’ - I have this

Table1

KeyT1
Table0Key
Some fields

Table2

KeyT2
Table1Key
NumericValue

Table0 -->> Table1 is a 1 to Many (KeyT0 -->> Table0Key)
Table1 -->Table2 is a 1 to many (KeyT1 -->> Table1Key)

I need to get:

  • some fields from Table1records where Table0Key = KeyT0
  • the Count of Children Table 1 has in table2 where Table1Key = KeyT0
  • The maximum value of NumericValue AMONG those children

Is it possible to do it in a single SQL Select?

I think could do the first along with either the 2nd OR 3rd in a single Select, but I am not sure how to do all 3 at once.

Can someone point me in the right direction?
Thanks,
-Karen

You can use a SELECT as a column and reference values from your master query, and that may be what you need here.

SELECT
  (SELECT count(*) FROM T2 WHERE key = master.key) AS t2_count
FROM 
  t1 AS master

Looking at your specs, if I understand it correctly, maybe something like this:

SELECT
  t1.col1,
  t1.col2, 
  (SELECT count(*) FROM table2 AS t2 WHERE t2.keyt1 = t1.key) AS count,
  (SELECT max(NumericValue) FROM table2 AS t2 WHERE t2.keyt1 = t1.key) AS max
FROM 
  table1 AS t1
WHERE
  t1.keyt0 = key0
1 Like

thanks, That was what i was trying in general to do but got lost in the text using full table and field names and was getting myself confused!

Then I got thinking those two sub selects should be a singe one and really went off the rails

-Karen

1 Like

That happens even to SQL nerds sometimes, no reason to be ashamed. You might find this article helpful as well: A Visual Explanation of SQLite Joins. The key word in SQL for dealing with multiple teams is “JOINS”. I’m not sure (as I’m not an SQLite expert) but I think with the exception of RIGHT OUTER JOINS SQLite is covering a lot of Join types.

Their charm is performance (at leat in “normal” databases), not sure about SQLite though.

What does not help me is that my first exposure to SQL was not really SQL, only an SQL like subset … and I did a LOT of coding in that product in the late 80’s/early 90s.

For example to this day i have to check if the order clause is "ORDER ON’ Or “ORDER BY” because that product used “ORDER BY”

It did not use the join terminology (which i can never keep straight) so I could only do “WHERE Table1.field = Table2.Field AND …” (which SQL also supports) and that can get one pretty far…

I have read through the join documentation many times, but just like “C” I find it unintuitive and hard to read!

So if I can, to join tables I always use “WHERE Table1.field = Table2.Field” instead of (is it?) LEFT OUTER JOIN with additional ANDs in the Where clause

That past experience combined with only very sporadic SQL use these days, always keeps the details fuzzy for me at the start of a DB based project.

-Karen

1 Like

But for that you are doing very well!

Don’t let me start on SQL and all the “dialects” :-), or even worse, something called SQL hints … in my former life I worked as performance optimiser for SAP . SQL hints are brand specific (DB/2, Oracle, etc.). This moment when you finally understood a huge nested SQL Statement only to find out that a lazy developer used SQL hints to achieve something slightly different w/o leaving any comments, priceless ;-).

Your SQL pain is my RegEx pain. Even though I made a lot of text parsing with Perl in the Nineties, I never found time so really get back to it afterward and never dived into regular expressions with Xojo. But I know it is a shame, as you can do very powerful stuff with it.