CODEWITHSUNDEEP
sqlsql-server
Adryan Permana
Adryan Permana

Reputation: 127

Query using a cross join from two selected tables in SQL Server

I have two queries:

Query for Table 1:

select a.[Kode AK] as KodeAK, b.Nama as NamaAK from RC_Member a left join 
(SELECT Kode_AK, Nama FROM OPENQUERY([ARMS],
' select Kode_AK,Nama, Tgl_insert from 
    (select ROW_NUMBER () over ( partition by Kode_AK order by Tgl_insert desc ) rn, 
        Kode_AK, Nama, Tgl_insert from KEANGGOTAAN.dbo.LOG_NAMA ) A where rn = 1'))
b on a.[Kode AK]  = b.Kode_AK

Query for Table 2:

select a.secCode as KodeStock,c.SEC_DSC as NamaStock from openquery(PDC_MYAPPS,'select * from mii.secReq')a left join (
      select * from RC_Saham
      )b on a.secCode=b.kode left join (select * from openquery([ARMS], 
        'select CODE_BASE_SEC,SEC_DSC from REFERENCES_DATA_DEV.dbo.PRODUCT_EQUITY'))c on a.secCode=c.CODE_BASE_SEC
      where b.[RC Sec (%)] is not null and b.[RC Sec (%)] ='0' and a.riskCharge !='0'

I want to use cross join the result of First query with the result of Second queries. I put cross join in the middle of those queries but it doesn't work.

How can I do it? Thanks

Upvotes: 0

Views: 120

Answers (3)

Pedram
Pedram

Reputation: 6508

Cross join can be used like below to use different selected tables query,

SELECT * FROM (
SELECT a.[Kode AK]  AS KodeAK
      ,b.Nama       AS NamaAK
FROM   RC_Member a
       LEFT JOIN (
                SELECT Kode_AK
                      ,Nama
                FROM   OPENQUERY(
                           [ARMS]
                          ,
                           ' select Kode_AK,Nama, Tgl_insert from 
    (select ROW_NUMBER () over ( partition by Kode_AK order by Tgl_insert desc ) rn, 
        Kode_AK, Nama, Tgl_insert from KEANGGOTAAN.dbo.LOG_NAMA ) A where rn = 1'
                       )
            )
            b
            ON  a.[Kode AK] = b.Kode_AK ) Query1
CROSS JOIN    
(SELECT a.secCode  AS KodeStock
      ,c.SEC_DSC  AS NamaStock
FROM   OPENQUERY(PDC_MYAPPS ,'select * from mii.secReq')a
       LEFT JOIN (
                SELECT *
                FROM   RC_Saham
            )b
            ON  a.secCode = b.kode
       LEFT JOIN (
                SELECT *
                FROM   OPENQUERY(
                           [ARMS]
                          ,
                           'select CODE_BASE_SEC,SEC_DSC from REFERENCES_DATA_DEV.dbo.PRODUCT_EQUITY'
                       )
            )c
            ON  a.secCode = c.CODE_BASE_SEC
WHERE  b.[RC Sec (%)] IS NOT NULL
       AND b.[RC Sec (%)] = '0'
       AND a.riskCharge!= '0') Query2

Also, you need to learn how to use Cross Join

Upvotes: 0

Fede
Fede

Reputation: 4016

How did you write the CROSS JOIN query? Something like this should work.

SELECT * FROM
(type your first query here) q1
CROSS JOIN
(type your second query here) q2;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Try this way

SELECT *
FROM   (SELECT a.secCode AS KodeStock,
               c.SEC_DSC AS NamaStock
        FROM   Openquery(PDC_MYAPPS, 'select * from mii.secReq')a
               LEFT JOIN (SELECT *
                          FROM   RC_Saham)b
                      ON a.secCode = b.kode
               LEFT JOIN (SELECT *
                          FROM   Openquery([ARMS], 'select CODE_BASE_SEC,SEC_DSC from REFERENCES_DATA_DEV.dbo.PRODUCT_EQUITY'))c
                      ON a.secCode = c.CODE_BASE_SEC
        WHERE  b.[RC Sec (%)] IS NOT NULL
               AND b.[RC Sec (%)] = '0'
               AND a.riskCharge != '0') a
       CROSS JOIN (SELECT a.[Kode AK] AS KodeAK,
                          b.Nama      AS NamaAK
                   FROM   RC_Member a
                          LEFT JOIN (SELECT Kode_AK,
                                            Nama
                                     FROM   Openquery([ARMS], ' select Kode_AK,Nama, Tgl_insert from 
    (select ROW_NUMBER () over ( partition by Kode_AK order by Tgl_insert desc ) rn, 
        Kode_AK, Nama, Tgl_insert from KEANGGOTAAN.dbo.LOG_NAMA ) A where rn = 1')) b
                                 ON a.[Kode AK] = b.Kode_AK) b

Upvotes: 0

Related Questions