1
\$\begingroup\$

I have two tables as below

ACCOUNTS:

+--------------+----------------+----------------------+-----------+
| BANK_ID (PK) | BRANCH_ID (PK) |    ACCOUNT_NUM  (PK) |  CURRENCY |
+--------------+----------------+----------------------+-----------+
|           20 |            621 |                 1001 |    ILS    |
|           20 |            623 |                 1002 |    USD    |
|           20 |             90 |                 1003 |    GBP    |
+--------------+----------------+----------------------+-----------+

AMOUNTS:

+-----------------+--------+-----+
| ACCOUNT_REC(PK) | AMOUNT |     |
+-----------------+--------+-----+
|     20 621 1001 |  10000 |     |
|     20 623 1002 |  20000 |     |
|     20 90  1003 |  30000 |     |
+-----------------+--------+-----+

and I want the result to be a simple join between the primary keys (without ACCOUNT_REC), i.e:

+---------+-----------+-------------+----------+--------+
| BANK_ID | BRANCH_ID | ACCOUNT_NUM | CURRENCY | AMOUNT |
+---------+-----------+-------------+----------+--------+
|      20 |       621 |        1001 | ILS      |  10000 |
|      20 |       623 |        1002 | USD      |  20000 |
|      20 |        90 |        1003 | GBP      |  30000 |
+---------+-----------+-------------+----------+--------+

My query is:

FROM [ACCOUNTS]  
  JOIN  [AMOUNTS]  
  ON (REPLACE( CAST  ([BANK_ID] as  nchar(2)) + ' '
       + CAST  ([BRANCH_ID] as  nchar(3))   + '  '
       + CAST  ([ACCOUNT_NUM] as  nchar(4)) ,' ', '') = REPLACE([ACCOUNT_REC], ' ', ''))

What I made to create a format that suits both tables [BANK_ID][BRANCH_ID][ACCOUNT_NUM] with no spaces between

I want to if there's any way to improve my query and if there's a better approach to this problem?

P.S: I cannot change the table structure

\$\endgroup\$
  • 1
    \$\begingroup\$ Aside from firing the database admin, I noticed you add spaces&remove them using REPLACE on both sides. \$\endgroup\$ – dustytrash Apr 4 at 15:02
  • \$\begingroup\$ The only good approach is to split the amounts.account_rec column into three separate columns. \$\endgroup\$ – Radim Bača Apr 4 at 18:16
0
\$\begingroup\$

You should probably fire the Database Admin.

But seriously, a problem in your query arises if you have duplicate, but different (bank_id + branch_id)'s. For example, (22 + 202) and (2 + 2202) both equal the same thing.

Change your query to include the space, instead of replacing it on both sides.

Here's a SQL Fiddle demonstrating the issue: http://sqlfiddle.com/#!18/3a6b9/2

Example query to match exactly, don't remove the spaces (Note won't work on SQL fiddle as I used different lengths for the colunmns):

SELECT BANK_ID, BRANCH_ID, ACCOUNT_NUM, CURRENCY, AMOUNT
FROM [ACCOUNTS]  
  JOIN  [AMOUNTS]  
  ON (
    CAST(
         CAST  ([BANK_ID] as  nchar(2)) + ' '
       + CAST  ([BRANCH_ID] as  nchar(3)) + ' '
       + CAST  ([ACCOUNT_NUM] as  nchar(4)) 
        AS varchar(255)
        ) 
         LIKE
        CAST ([AMOUNTS].[ACCOUNT_REC] AS varchar(255))
     )

Update, since you mentioned more about your schema:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f8c037b9cc9afc2e2de850011031020d

SELECT  * FROM ACCOUNTS JOIN AMOUNTS ON (
         CONCAT(CONVERT(VARCHAR(255), [BANK_ID]), ' ')
       + CONCAT(CONVERT(VARCHAR(255), [BRANCH_ID]), ' ')
       + CONVERT(VARCHAR(255), [ACCOUNT_NUM])
       ) LIKE ([AMOUNTS].[ACCOUNT_REC] )
\$\endgroup\$
  • \$\begingroup\$ Your query returns an empty set \$\endgroup\$ – styx Apr 14 at 5:41
  • \$\begingroup\$ @styx as mentioned, you may need to change the values inside 'nchar(x)' to match your database. \$\endgroup\$ – dustytrash Apr 14 at 7:41
  • \$\begingroup\$ all but ACCOUNT_REC are defined as INT \$\endgroup\$ – styx Apr 14 at 7:48
  • \$\begingroup\$ @styx updated my answer \$\endgroup\$ – dustytrash Apr 14 at 8:17
  • \$\begingroup\$ your fiddle is correct, but still, for some reason, i get an empty result \$\endgroup\$ – styx Apr 14 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.