CODEWITHSUNDEEP
sqlpostgresql
leu
leu

Reputation: 125

How to JOIN in the order given by the joined table

I have two tables in a postgresql v10 database. First table stores creditcard slips, second table stores invoices. I want to to find the corresponding invoices for every creditcard transaction based on the transaction date and the transaction amount.

 SELECT cap."slipNr",
    belege."rechNum",
    cap."transactionAmountOriginal",
    belege."rechSum",
    cap."transactionDate",
    belege."rechDate"
   FROM ("2016".cap
     JOIN "2016".belege ON (((cap."transactionDate" = belege."rechDate") AND (cap."transactionAmountOriginal" = belege."rechSum"))))
  WHERE ((cap."transactionDate" = belege."rechDate") AND (cap."transactionAmountOriginal" = belege."rechSum"))
  ORDER BY cap."slipNr"

Everything works as expected until there are more than one invoice per day with a matching transactionAmount. Of course I can solve that by SELECT DISTINCT ON (cap.slipNr), however, as you can see on the screenshot, the query does not sort the invoices table but rather takes a random matching entry. The first corresponding entry for slipNr is rechNum 2242 instead of the real first entry in the table which is 2236.

The belege.rechNum has an ascending index. The data in the "belege" table was inserted ordered by "rechNum"

slipNr | rechNum | transactionAmountOriginal | rechSum | transactionDate |  rechDate
--------+---------+---------------------------+---------+-----------------+------------
   6196 |    2230 |                    234.30 |  234.30 | 2016-01-02      | 2016-01-02
   6197 |    2232 |                     69.90 |   69.90 | 2016-01-02      | 2016-01-02
   6198 |    2234 |                    106.80 |  106.80 | 2016-01-02      | 2016-01-02
   6199 |    2235 |                     54.80 |   54.80 | 2016-01-02      | 2016-01-02
   6201 |    2242 |                     41.00 |   41.00 | 2016-01-04      | 2016-01-04
   6201 |    2236 |                     41.00 |   41.00 | 2016-01-04      | 2016-01-04
   6201 |    2237 |                     41.00 |   41.00 | 2016-01-04      | 2016-01-04
   6201 |    2241 |                     41.00 |   41.00 | 2016-01-04      | 2016-01-04
   6202 |    2238 |                     55.40 |   55.40 | 2016-01-04      | 2016-01-04

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

When you use distinct on, you need to adjust the order by to match. So I think you want:

order by cap.slipNr, belege.rechNum

If you want the final result set in a different order, use a subquery or CTE and another order by.

Upvotes: 1

Related Questions