-1
query="SELECT a.id, b.id
        FROM tab_a a, tab_b b
        WHERE a.ref = b.ref
        AND a.amount = -b.amount
        AND NOT a.tot AND NOT b.tot
        AND a.a_id = %(a_id)s AND b.a_id = %(a_id)s
        {p_id_condition}
        ORDER BY a.date desc"

i am trying

first try to match the ref, but if no pair found, try to match the amount

harsh
  • 97
  • 1
  • 7
  • 2
    What? What is this query doing, which RDBMS ? – sagi Aug 29 '17 at 13:21
  • How about using an `OR` in the `WHERE`-clause? – waka Aug 29 '17 at 13:21
  • Does your query execute without error? How do you want to use `case`, or do you mean something else entirely? Please elucidate. – HoneyBadger Aug 29 '17 at 13:27
  • Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Aug 29 '17 at 13:38

1 Answers1

1

Would something like this work:

SELECT
    a.id,
    b.id
FROM tab_a a
INNER JOIN tab_b b
    ON a.ref = b.ref OR
       a.amount = -b.amount
WHERE
    NOT a.tot AND
    NOT b.tot AND
    a.a_id = %(a_id)s AND
    b.a_id = %(a_id)s
    {p_id_condition}
ORDER BY a.date DESC

I rewrote your query using explicit join syntax, which isolated and revealed the two join conditions you mentioned in your question. Then I changed the and to an or, which would seem to be what you want.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360