CODEWITHSUNDEEP
sqlsql-server
mHelpMe
mHelpMe

Reputation: 6668

SQL server inner join on multiple columns with where condition

I have the query below where I am try to compare two tables. I can see the two tables do have matching rows but the query below does not return any rows. I have followed the examples from similar questions but I cannot get the query to work - not sure what I am missing? I did read about using the a union query to compare two tables, would that be better?

SELECT H.[FUND_CD], H.[SEDOLCHK], H.[CURR]
FROM 
    HOLDINGS H
INNER JOIN 
    HOLDINGS_LIVE HL1 ON H.FUND_CD = HL1.FUND_CD
INNER JOIN 
    HOLDINGS_LIVE HL2 ON H.SEDOLCHK = HL2.SEDOLCHK
INNER JOIN 
    HOLDINGS_LIVE HL3 ON H.SEDOLCHK = HL3.CURR
WHERE 
    H.DATEU = '2014-03-06' AND HL1.DATEU = '2014-03-06'

EDIT

Please see the data the return should be returning. I have changed the fund & ID columns, however the columns are strings. So what I would like my query to do is show me where any rows are not exactly the same. For example the second row in Table B has a nominal which is different to Table A. The prices in table B are also different to column A.

Table A                 
Fund    ID  Nominal Currency    Price   Date Stamp
ABC 12345   34102   GBP 1257    06-03-2014 00:00:00
ABC 45678   14884   EUR 13.085  06-03-2014 00:00:00
ABC 32564   2404    EUR 65.97   06-03-2014 00:00:00
ABC 95874   13515   CHF 87  06-03-2014 00:00:00
ABC 96325   803201  GBP 247.3   06-03-2014 00:00:00
ABC 15648   39442   DKK 256.7   06-03-2014 00:00:00
DEF 78451   1761    USD 372.16  06-03-2014 00:00:00
DEF 48884   22936   USD 13.99   06-03-2014 00:00:00
DEF 33215   2288    USD 41.1    06-03-2014 00:00:00
DEF 68745   801 CAD 86.16   06-03-2014 00:00:00

Table B                 
Fund    ID  Nominal Currency    Price   Date Stamp
ABC 12345   34102   GBP 12.57   06-03-2014 00:00:00
ABC 45678   14800   EUR 0.13085 06-03-2014 00:00:00
ABC 32564   2404    EUR 0.6597  06-03-2014 00:00:00
ABC 95874   13515   CHF 0.87    06-03-2014 00:00:00
ABC 96325   803201  GBP 2.473   06-03-2014 00:00:00
ABC 15648   39442   DKK 2.567   06-03-2014 00:00:00
DEF 78451   1761    USD 3.7216  06-03-2014 00:00:00
DEF 48884   22936   USD 0.1399  06-03-2014 00:00:00  
DEF 33215   2288    USD 0.411   06-03-2014 00:00:00
DEF 68745   801 CAD 0.8616  06-03-2014 00:00:00

Latest query attempt

SELECT H.[FUND_CD], H.[SEDOLCHK], H.[CURR]
FROM 
    HOLDINGS H
INNER JOIN 
    HOLDINGS_LIVE HL 
    ON 
    ( 
        H.FUND_CD = HL.FUND_CD
        AND H.SEDOLCHK = HL.SEDOLCHK
        AND H.CURR = HL.CURR
    )

Upvotes: 1

Views: 102

Answers (2)

SQB
SQB

Reputation: 4078

Two observations (too long for a comment).

First off, don't you mean your third join to be 

INNER JOIN HOLDINGS_LIVE HL3 ON H.CURR = HL3.CURR

Also, you're now joining the same table (HOLDINGS_LIVE) thrice. Could you possibly have meant to join it on three conditions?

SELECT H.[FUND_CD], H.[SEDOLCHK], H.[CURR]
FROM HOLDINGS H
INNER JOIN HOLDINGS_LIVE HL 
   ON (   H.FUND_CD = HL.FUND_CD 
      AND H.SEDOLCHK = HL.SEDOLCHK 
      AND H.CURR = HL.CURR
   )
WHERE H.DATEU = '2014-03-06' AND HL.DATEU = '2014-03-06'

Lastly, what happens if you drop the where-clause? Is the date format you use correct? If the field is of a date datatype, I advise you to use explicit conversion instead of relying on implicit conversion.

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

you can use except:

SELECT H.[FUND_CD], H.[SEDOLCHK], H.[CURR]FROM HOLDINGS H
except 
SELECT H.[FUND_CD], H1.[SEDOLCHK], H.[CURR]FROM HOLDINGS_LIVE H

Upvotes: 0

Related Questions