Sql query with multi-level subqueries

Question

I have an SQL query to get info from a kindergarten presence form inside a joomla website and do some maths to know how much the kids parent should pay each week then sum it to know how much for the tax receipt.

When I use just one kid I get the correct amount, but as soon as I add the second the numbers don't add up with both kids.

Here is the request:

SELECT
    SUM(t1.test1) AS t1,
    SUM(t2.test2) AS t2
FROM
(
    SELECT
        (COUNT(josnh_facileforms_records.id) * sal1.salaire1) AS test1,
        josnh_facileforms_subrecords.record
    FROM josnh_facileforms_records,
         josnh_facileforms_subrecords
    LEFT JOIN
    (
        SELECT
            LEFT(josnh_facileforms_subrecords.value, 5) AS salaire1,
            josnh_facileforms_subrecords.record AS records1
        FROM josnh_facileforms_records,
             josnh_facileforms_subrecords
        WHERE josnh_facileforms_records.user_id = [id]
              AND josnh_facileforms_records.form = 24
              AND josnh_facileforms_records.id = josnh_facileforms_subrecords.record
              AND josnh_facileforms_subrecords.name LIKE 'enfant1'
        GROUP BY
            josnh_facileforms_subrecords.record
    ) AS sal1 ON sal1.records1 = josnh_facileforms_subrecords.record
    WHERE josnh_facileforms_records.user_id = [id]
          AND josnh_facileforms_records.form = 24
          AND josnh_facileforms_records.id = josnh_facileforms_subrecords.record
          AND josnh_facileforms_subrecords.name LIKE '%di1'
          AND josnh_facileforms_subrecords.value = 'RG'
    GROUP BY
        josnh_facileforms_subrecords.record
) AS t1,
(
    SELECT
        (COUNT(josnh_facileforms_records.id) * sal2.salaire2) AS test2,
        josnh_facileforms_subrecords.record
    FROM josnh_facileforms_records,
         josnh_facileforms_subrecords
    LEFT JOIN
    (
        SELECT
            LEFT(josnh_facileforms_subrecords.value, 5) AS salaire2,
            josnh_facileforms_subrecords.record AS records2
        FROM josnh_facileforms_records,
             josnh_facileforms_subrecords
        WHERE josnh_facileforms_records.user_id = [id]
              AND josnh_facileforms_records.form = 24
              AND josnh_facileforms_records.id = josnh_facileforms_subrecords.record
              AND josnh_facileforms_subrecords.name LIKE 'enfant2'
        GROUP BY
            josnh_facileforms_subrecords.record
    ) AS sal2 ON sal2.records2 = josnh_facileforms_subrecords.record
    WHERE josnh_facileforms_records.user_id = [id]
          AND josnh_facileforms_records.form = 24
          AND josnh_facileforms_records.id = josnh_facileforms_subrecords.record
          AND josnh_facileforms_subrecords.name LIKE '%di2'
          AND josnh_facileforms_subrecords.value = 'RG'
    GROUP BY
        josnh_facileforms_subrecords.record
) AS t2;

Can I get a second look at this?

what do you mean did not add up for both? do you mean `(sum(t1.test1) + sum(t2.test2)) as Grand_Total` — LONG, Jul 11 '17 at 13:56
hum yes sorry i mean the end numbers don't add up kid1 -> sum(t1.test1) alone in the query alone gives 189.66 in my tests which is the correct number. but when i add the kid2 part i get : kid1 = 379.3199999999999994 kid2 = 401.2999999999999995 weird huh?? — ddesbiens, Jul 11 '17 at 13:58
btw this part refers the joomla user id so obviously i changed the value before posting : josnh_facileforms_records.user_id = [id] — ddesbiens, Jul 11 '17 at 14:08
after looking at the resulting numbers i just realized that it simply double the ending numbers with trailing floats. Obviously there's something wrong with the request but i can't seem to put my finger on it. — ddesbiens, Jul 11 '17 at 14:22
i think i found a clue adding the 2 subqueries t1 and t2 in the FROM part without any linking betwween them might do this. — ddesbiens, Jul 11 '17 at 14:42

score 0 · Accepted Answer · answered Jul 11 '17 at 15:23

0

alright it was a newbie problem i was so focused on the subqueries that i forgot to link the subquery in the FROM tag with a left tag.

answered Jul 11 '17 at 15:23

ddesbiens

1
1

Sql query with multi-level subqueries

1 Answers1