CODEWITHSUNDEEP
mysqlsqlsqlite
harryg
harryg

Reputation: 24077

Why doesn't this query work in SQLite but works fine in MySQL and MSAccess?

I have a pretty complex SQL query as shown in this fiddle

SELECT payer_payment.payer_id, 
       Sum(payer_payment.amount)                             AS total_paid, 
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share 
FROM   payers 
       INNER JOIN (payer_payment 
                   INNER JOIN (SELECT payment_id, 
                                      Sum(amount) / Sum(pays) AS single_share 
                               FROM   payer_payment 
                               GROUP  BY payment_id) AS payments_share 
                           ON payer_payment.payment_id = 
                              payments_share.payment_id) 
               ON payers.id = payer_payment.payer_id 
WHERE  payers.user_id  = 1 
GROUP  BY payer_payment.payer_id;

In the fiddle it runs fine on MySQL but when I run it on a SQLite database it either throws an error citing:

(1 no such column: payer_payment.payer_id)

When the column clearly does exist.

or simply returns 0 results, depending on the SQLite implementation (WebSQL vs SQLite.js)

What is the reason for this and is it possible to make my query more database agnostic?

Upvotes: 1

Views: 229

Answers (1)

CL.
CL.

Reputation: 180020

In SQLite, you get an autoincrementing column by using INTEGER PRIMARY KEY. (And if you rely on the actual values of payers.id in the query, you should give them explicitly.)

You should not try to nest joins when it is not needed:

SELECT payer_payment.payer_id,
       Sum(payer_payment.amount)                             AS total_paid,
       Sum(payer_payment.pays * payments_share.single_share) AS fair_share
FROM       payers
INNER JOIN payer_payment
        ON payers.id = payer_payment.payer_id
INNER JOIN (SELECT payment_id,
                   Sum(amount) / Sum(pays) AS single_share
            FROM   payer_payment
            GROUP  BY payment_id) AS payments_share
        ON payer_payment.payment_id = payments_share.payment_id
WHERE payers.user_id = 1
GROUP BY payer_payment.payer_id;

SQLFiddle

Upvotes: 1

Related Questions