Merge & display records from two different tables

Question

I have to create a view using 2 tables

Table 1:

TokenNumber     Symbol      InstType    LotSize     TickSize   sin              ClosePrice
------------------------------------------------------------------------------------------
22              ACC         EQ          1           0.01       INE012A01025     0
25              ADANIENT    EQ          1           0.01       INE423A01024     0

Table 2:

TokenNumber     InstType   Symbol     ExpiryDate    CMToken    sorted_index
---------------------------------------------------------------------------
46676           FUTSTK     ACC        25-Jan-18     22         12
48205           FUTSTK     ACC        28-Dec-17     22         8
4820546676      FUTSTK     ACC        28-Dec-17     22         3
5361748205      FUTSTK     ACC        30-Nov-17     22         1
53618           FUTSTK     ADANIENT   30-Nov-17     25         3
4820646677      FUTSTK     ADANIENT   28-Dec-17     25         3
48206           FUTSTK     ADANIENT   28-Dec-17     25         8
46677           FUTSTK     ADANIENT   25-Jan-18     25         12

Thus to join both tables we have primary key of Table1(TokenNumber) in Table2(CMToken).

I have to show all the columns of both tables and records in a specific order(1st record from Table1 and next all records from Table2 where Table1.TokenNumber=Table2.CMToken).

New view with record order will be like:

TokenNumber  Symbol       InstType   LotSize    TickSize   Isin           ClosePrice    ExpiryDate      sorted_index
--------------------------------------------------------------------------------------------------------------------                                    
22           ACC          EQ         1          0.01       INE012A01025   0             NULL            NULL
46676        ACC          FUTSTK     NULL       NULL       NULL           NULL          25-Jan-18       0
48205        ACC          FUTSTK     NULL       NULL       NULL           NULL          28-Dec-17       1
4820546676   ACC          FUTSTK     NULL       NULL       NULL           NULL          28-Dec-17       2                                   
25           ADANIENT     EQ         1          0.01       INE423A01024   0             NULL            NULL
53618        ADANIENT     FUTSTK     NULL       NULL       NULL           NULL          30-Nov-17       0
4820646677   ADANIENT     FUTSTK     NULL       NULL       NULL           NULL          28-Dec-17       1
48206        ADANIENT     FUTSTK     NULL       NULL       NULL           NULL          28-Dec-17       3
46677        ADANIENT     FUTSTK     NULL       NULL       NULL           NULL          25-Jan-18       2

Please suggest the query.

Thanks & Regards

Are you using MySQL or MS SQL Server? Don't tag products not involved. — jarlh, Nov 16 '17 at 08:31
@AshishJaiswal: Do the data of `table2` need to be sorted on the base of `TokenNumber`? and Do you need to populate `sorted_index` column at run time? — zarruq, Nov 16 '17 at 08:41

zarruq · Answer 1 · 2017-11-16T14:04:50.830

Looking in to your sample data and output, I think you actually want to assign sorted_index = NULL to values of table1 and sorted_index = row_number() partition by CMToken and order by TokenNumber starting from 0 to table2. After that you want to sort it by placing row of table1 on top of associated rows of table2 and among table2 rows, you want to sort it by row_number().

SELECT TokenNumber
    ,Symbol
    ,InstType
    ,LotSize
    ,TickSize
    ,Isin
    ,ClosePrice
    ,Expiry_Date
    ,sorted_index
FROM (
    SELECT TokenNumber
        ,Symbol
        ,InstType
        ,LotSize
        ,TickSize
        ,Isin
        ,ClosePrice
        ,NULL AS Expiry_Date
        ,NULL AS sorted_index
        ,TokenNumber AS CMToken
    FROM table1 t1

    UNION ALL

    SELECT TokenNumber
        ,InstType
        ,Symbol
        ,NULL
        ,NULL
        ,NULL
        ,NULL
        ,ExpiryDate
        ,row_number() OVER (
            PARTITION BY CMToken ORDER BY TokenNumber
            ) - 1 AS sorted_index
        ,CMToken
    FROM table2 t2
    ) t
ORDER BY CMToken
    ,sorted_index

Result:

 +-------------+----------+----------+---------+----------+--------------+------------+---------------------+--------------+
| TokenNumber |  Symbol  | InstType | LotSize | TickSize |     Isin     | ClosePrice |     Expiry_Date     | sorted_index |
+-------------+----------+----------+---------+----------+--------------+------------+---------------------+--------------+
|          22 | ACC      | EQ       | 1       | 0,01     | INE012A01025 | 0          | NULL                | NULL         |
|       46676 | FUTSTK   | ACC      | NULL    | NULL     | NULL         | NULL       | 25.01.2018 00:00:00 | 0            |
|       48205 | FUTSTK   | ACC      | NULL    | NULL     | NULL         | NULL       | 28.12.2017 00:00:00 | 1            |
|  4820546676 | FUTSTK   | ACC      | NULL    | NULL     | NULL         | NULL       | 28.12.2017 00:00:00 | 2            |
|  5361748205 | FUTSTK   | ACC      | NULL    | NULL     | NULL         | NULL       | 30.11.2017 00:00:00 | 3            |
|          25 | ADANIENT | EQ       | 1       | 0,01     | INE423A01024 | 0          | NULL                | NULL         |
|       46677 | FUTSTK   | ADANIENT | NULL    | NULL     | NULL         | NULL       | 25.01.2018 00:00:00 | 0            |
|       48206 | FUTSTK   | ADANIENT | NULL    | NULL     | NULL         | NULL       | 28.12.2017 00:00:00 | 1            |
|       53618 | FUTSTK   | ADANIENT | NULL    | NULL     | NULL         | NULL       | 30.11.2017 00:00:00 | 2            |
|  4820646677 | FUTSTK   | ADANIENT | NULL    | NULL     | NULL         | NULL       | 28.12.2017 00:00:00 | 3            |
+-------------+----------+----------+---------+----------+--------------+------------+---------------------+--------------+

DEMO

Getting error as Msg 207, Level 16, State 1, Line 34 Invalid column name 'CMToken'. Msg 207, Level 16, State 1, Line 36 Invalid column name 'CMToken'. — Ashish Jaiswal, Nov 16 '17 at 11:33
Actually I want to simply display all the column values in every row as if no data exist display as NULL else the value. — Ashish Jaiswal, Nov 16 '17 at 11:35
@AshishJaiswal: check the associated sql-server demo provided in answer. The query is simulated on your provided data. Perhaps you missed something. — zarruq, Nov 16 '17 at 12:17

Merge & display records from two different tables

1 Answers1