Query problem with "AND" "OR" "LIKE"

[pascale]

Hi, I have created a query in SQL that gives me the answer I want for one element, but i would like it to give me the correct answer for several elementSELECT "GEN", "debit", "credit", "debit" - "credit" "Solde" FROM ( SELECT ( "ID" ) "GEN" FROM "General" WHERE "ID" = '445663' ), ( SELECT SUM( "Montant" ) "debit" FROM "MONGENDEB" WHERE MONTH( "Date" ) = :MOIS AND "Debit" = '445663' ), ( SELECT SUM( "Montant" ) "credit" FROM "MONGENCRED" WHERE MONTH( "Date" ) = :MOIS AND "Credit" = '445663' )I would like to have the reply for 445663, 445662, 445000 and several others, I have tried “AND”, “OR” and “LIKE” with varying result from “Syntax error” to “Not in this Table”.
Any help or advise will be greatly appreciated
Kind regards
Paul

[Ratslinger]

Hello,

Kind of looks like the last similar problem - here.

When having multiple choices, best to enclose in parenthesis. So instead of:

WHERE "ID" = '445663'

you might have:

WHERE ("ID" = '445663' OR "ID" = '445662' OR "ID" = '445000')

EDIT:

Based upon your selection including ‘ID’, it appears you want separate records with totals for each. Here is a working statement:

SELECT *
FROM (SELECT ID,
             DEBIT,
             CREDIT,
             CASE  WHEN DEBIT IS NULL THEN 0 ELSE DEBIT  END 
      - CASE WHEN CREDIT IS NULL THEN 0  ELSE CREDIT END "Solde"
      FROM "General"
        LEFT JOIN (SELECT "Debit",
                          SUM("MONGENDEB"."Montant") DEBIT
                   FROM "MONGENDEB"
                   WHERE MONTH("MONGENDEB"."Date") = :MOIS
                   GROUP BY "Debit") A ON "General"."ID" = "A"."Debit"
        LEFT JOIN (SELECT "Credit",
                          SUM("MONGENCRED"."Montant") CREDIT
                   FROM "MONGENCRED"
                   WHERE MONTH("MONGENCRED"."Date") = :MOIS
                   GROUP BY "Credit") B ON "General"."ID" = "B"."Credit")
WHERE ID = '445663'
OR    ID = '445662'
OR    ID = '445000'

I would suggest you take another look at your setup or, as you can see, get more knowledge on SQL. This took quite a while since I worked only with the embedded version not knowing what you had.

[pascale]

HI again, yes it is similar !! I have tried your advise but it gives me the two lines with the same total (the correct total for the first line repeated on both), I have added the same parentheses to the “credit” and “debit” part of the query, but this then gives a different total (sum of both lines) repeated again on both lines. I think I need to start again with this query, attack from a different angle and try to use the “ID” column to somehow generate the totals. Thank you for rapid reply,

[pascale]

(continued) I will get back to you tomorrow morning. Goodnight from Bordeaux. Regards Paul

[Ratslinger]

@pascale Not exactly sure what result you want - One set of totals based upon multiple accounts or separate totals for different selected accounts? Also, don’t remember if you ever stated - are you using an embedded or split DB? Knowing this greatly helps when dealing with SQL as embedded has less capabilities.

[pascale]

Hi Mr @Ratslinger, yes you are right again you SQL statement is Just what I need, it does exactly what I want, AGAIN thank you so much for your help!!!
To answer your questions; Iam using embedded, so would you advise that I set up a MYSQL separately as this will be a large database?
I am trying to learn SQL as I go, My tables are based on this link text document but here she is using ms base and the SQL is different to base 5.
Again many thanks

[Ratslinger]

@pascale Definitely move away from embedded. Move either to a split DB or another such as MySQL, Maria DB, PostgreSQL or others. Embedded will eventually give you problems.

[pascale]

Thanks again for your help. I looked at link text which explains using outside DB.
Regards paul