[SOLVED] Help with SQL

[pcurtis]

I know this isn't about FPC / LAZ, but where can I get help with creating  a SQL (MariaDB) query?

Thanks in advance.

[Zvoni]

SQLFiddle? Beware: MySQL 5.6 only (no idea which Version of MariaDB that would be)
http://sqlfiddle.com/

DB-Fiddle
https://www.db-fiddle.com/

OTOH, if you don't get it, just post it here (if it's acceptable with Forum-policy), and we'll try to help.
I'm doing that in other forums i'm a member of for some years now, and noone ever complained, since other people can learn something from it

[pcurtis]

Thanks. I'll have a look. I will also post my issue

I would like to update tnTOPICS and set fnCOUNT = number rows returned from tnURLS only where tnTOPIC.fnIDX = tnURLS.fnTOPIC_IDX and fnGOTDATA = 0

CREATE TABLE `tnTOPICS` (   
  `fnIDX` int(11) NOT NULL AUTO_INCREMENT, 
  `fnDESCRIPTION` varchar(50) DEFAULT '', 
  `fnCOUNT` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`fnIDX`)
) ENGINE=InnoDB;

INSERT INTO `tnTOPICS` 
(`fnIDX`, `fnDESCRIPTION`, `fnCOUNT`) 
VALUES 
(1, 'TOPIC1', 0), 
(2, 'TOPIC2', 0), 
(3, 'TOPIC3', 0), 
(4, 'TOPIC4', 7), 
(5, 'TOPIC5', 3); 

CREATE TABLE `tnURLS` ( 
`fnIDX` int(11) NOT NULL AUTO_INCREMENT, 
`fnTOPIC_IDX` int(11) NOT NULL DEFAULT 0, 
`fnGOTDATA` tinyint(4) NOT NULL DEFAULT 0, 
PRIMARY KEY (`fnIDX`)
) ENGINE=InnoDB;

INSERT INTO `tnURLS` 
(`fnIDX`, `fnTOPIC_IDX`, `fnGOTDATA`) 
VALUES 
(1, 1, 1), 
(2, 1, 0), 
(3, 1, 0), 
(4, 2, 1), 
(5, 2, 1), 
(6, 2, 1), 
(7, 3, 0), 
(8, 3, 0), 
(9, 3, 0), 
(10, 1, 1), 
(11, 2, 1), 
(12, 3, 1);

Table tnTOPICS should look like this after the update

1 TOPIC1 2 
2 TOPIC2 4 
3 TOPIC3 1 
4 TOPIC4 0 
5 TOPIC5 3

Thanks for your help.

[Zvoni]

and fnGOTDATA = 0 doesn't make sense
You expect for Topic2 to return 4, but all Topic2 in tnURLS has fnGOTDATA = 1, not 0

i think it should be !...and fnGOTDATA=1"

EDIT: Your expected result for Topic4 to be 0 also doesn't make sense (compared to Topic5), since you don't have any Detail in tnURLS for Topic4
Should expect Topic4 7 as result

1 TOPIC1 2
2 TOPIC2 4
3 TOPIC3 1
4 TOPIC4 7
5 TOPIC5 3

[Zvoni]

Code: MySQL  [Select][+][-]

1. SELECT DISTINCT
2. T1.fnIDX,
3. T1.fnDescription,
4. (CASE WHEN T2.fnTOPIC_IDX IS NULL THEN T1.fnCOUNT ELSE Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX) END) As fnCount
5. FROM
6. tnTopics T1
7. LEFT JOIN
8. tnURLS T2
9. ON
10. T2.fnTOPIC_IDX=T1.fnIDX AND
11. T2.fnGOTDATA=1
12. ORDER BY
13. T1.fnIDX
14.  

Returns:

Code: Text  [Select][+][-]

1. fnIDX   fnDESCRIPTION   fnCount
2. 1       TOPIC1              2
3. 2       TOPIC2              4
4. 3       TOPIC3              1
5. 4       TOPIC4              7
6. 5       TOPIC5              3

Tried with SQLite, but i don't see anything specific which wouldn't work for MySQL/MariaDB

EDIT: Or is it the other way round? Are you expecting TOPIC4 and TOPIC5 to be set to 0?
If yes, then Line 4 of the SQL should be

Code: MySQL  [Select][+][-]

1. Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX) As fnCount
2.  

would return:

Code: Text  [Select][+][-]

1. fnIDX   fnDESCRIPTION   fnCount
2. 1       TOPIC1              2
3. 2       TOPIC2              4
4. 3       TOPIC3              1
5. 4       TOPIC4              0
6. 5       TOPIC5              0

EDIT2: Just saw your Signature.....
Laz 2.10.0 ???? 
Are our boys (and girls?) withholding the newest Lazarus-Version from us?

[pcurtis]

Thanks so much. Now the work can begin.

[Zvoni]

Please note, that this is only the SELECT-Part of the SQL

You want to UPDATE tnTOPICS Column fnCOUNT
so you would need an UPDATE-Statement, but i hope you know how to go from here

Should be something like (Aircode)

Code: MySQL  [Select][+][-]

1. UPDATE A1
2. SET A1.fnCOUNT=A2.fnCOUNT
3. FROM
4. tnTOPICS As A1,
5. (HERE GOES THE ABOVE SELECT-STATEMENT) As A2
6. WHERE
7. A1.fnIDX=A2.fnIDX
8.  

[pcurtis]

OK. I've tried this

Code: Pascal  [Select][+][-]

1. UPDATE A1
2. SET A1.fnCOUNT = A2.fnCOUNT
3. FROM
4.   tnTOPICS AS A1,
5.   (SELECT DISTINCT
6.   T1.fnIDX,
7.   T1.fnDESCRIPTION,
8.   (CASE WHEN T2.fnTOPIC_IDX IS NULL THEN T1.fnCOUNT ELSE Count(T2.fnIDX) OVER(PARTITION BY T2.fnTOPIC_IDX) END) As fnCOUNT
9.   FROM
10.     tnTOPICS T1
11.   LEFT JOIN
12.     tnURLS T2
13.   ON
14.     T2.fnTOPIC_IDX = T1.fnIDX AND
15.     T2.fnGOTDATA = 0
16.   ORDER BY
17.     T1.fnIDX) AS A2
18. WHERE
19.   A1.fnIDX = A2.fnIDX
20.  

and it doesn't work.

It complains of a syntax error

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM
  tnTOPICS AS A1,
  (SELECT DISTINCT
  T1.fnIDX,
  T1.fnDESCRIPTION,...' at line 3 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec. */

 The SELECT statement works fine on it's own.

Any ideas?

[Zvoni]

Damn! I've seen that error a lot.

Found this:
https://stackoverflow.com/questions/10262300/syntax-error-near-from-when-using-update-with-join-in-mysql
Looks like you have to join first, and then SET the new Value

[pcurtis]

I hate JOIN's

I think I'm close with this, and JOIN free 

Code: Pascal  [Select][+][-]

1. UPDATE tnTOPIC
2. SET tnTOPIC.fnCOUNT = (
3.   SELECT DISTINCT
4.   COUNT(tnURL.fnIDX) OVER (PARTITION BY tnURL.fnTOPIC_IDX)
5. FROM
6.   tnURL
7. WHERE
8.   tnURL.fnTOPIC_IDX = tnTOPIC.fnIDX
9. AND
10.   tnURL.fnGOTIMAGE = 0   
11. )
12. WHERE
13.   fnCOUNT > 0
14.  

[Zvoni]

be careful!
your WHERE fnCount>0 filters to the original data in tnTOPICS, not the Count from the inner Query.
So you would set only those records which have fnCount>0 before counting the URL's

EDIT
After thinking about it:
Have you thought to make a View out of the Inner Query?
The Inner query is static (no dynamic filters), so you could address it as a regular table

[pcurtis]

You've lost me. I haven't got a clue about views and such.

[pcurtis]

OK, I bit the bullet and tried this

Code: Pascal  [Select][+][-]

1. UPDATE tnTOPIC AS T1
2. INNER JOIN tnURL AS T2 ON T1.fnIDX = T2.fnTOPIC_IDX
3. SET T1.fnCOUNT = (
4.                   SELECT DISTINCT
5.                     COUNT(tnURL.fnIDX) OVER (PARTITION BY tnURL.fnTOPIC_IDX)
6.                   FROM
7.                     tnURL
8.                   WHERE
9.                     tnURL.fnTOPIC_IDX = T1.fnIDX
10.                   AND
11.                     tnURL.fnGOTIMAGE = 0  
12.                  )
13.  

Any comments / notes?

[Zvoni]

Looks exactly the way it's supposed to work with MySQL

[pcurtis]

When I execute it on small tables (tnTOPICS = 5 records, tnURLS = 13 records) it execute fast, 0.15 secs.
When I try it on real data (tnTOPICS = 30000 records, tnURLS = 250000 records) it didn't finish after 20 minutes?

Both tables have primary indexes.