2
\$\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\$
2
  • 2
    \$\begingroup\$ Aside from firing the database admin, I noticed you add spaces&remove them using REPLACE on both sides. \$\endgroup\$
    – dustytrash
    Apr 4 '19 at 15:02
  • 1
    \$\begingroup\$ The only good approach is to split the amounts.account_rec column into three separate columns. \$\endgroup\$ Apr 4 '19 at 18:16
2
\$\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\$
7
  • \$\begingroup\$ Your query returns an empty set \$\endgroup\$
    – styx
    Apr 14 '19 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 '19 at 7:41
  • \$\begingroup\$ all but ACCOUNT_REC are defined as INT \$\endgroup\$
    – styx
    Apr 14 '19 at 7:48
  • \$\begingroup\$ @styx updated my answer \$\endgroup\$
    – dustytrash
    Apr 14 '19 at 8:17
  • \$\begingroup\$ your fiddle is correct, but still, for some reason, i get an empty result \$\endgroup\$
    – styx
    Apr 14 '19 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.