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

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

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.

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,

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

@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.

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

@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.

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