Sql Server DW - Multiple join

  • pdsqsql

    SSC Veteran

    Points: 238

    I am trying to create a view from one DW Database tables to another DW database (which is empty).

    I have total 5 tables - 4 tables in one schema and another table is in another schema from same Source database.

    Morg.loanNum and DevProg.loanID has 1 to many relationship.

    I am getting following error:

    ERROR: Msg 4104, Level 16, State 1, Line 31

    --The multi-part identifier "BD.Morg.loan" could not be bound.

    Because BD.Morg.loanNum and AD.DevProg.loanID has 1 to many relationship.

    How can use join as i tried with LEFT OUTER JOIN, CROSS JOIN also.

    My Sql is: ( i am not creating view right now as trying work on select first)

    SELECT DISTINCT

    AD.Dev.DevName,

    AD.Cnty.CntyName,

    BD.Morg.loanNum, BD.Morg.stat, BD.Morg.OPbal, BD.Morg.CPbal,

    BD.Morg.Ebal, AD.DevProg.loanID

    FROM

    AD.Addrs INNER JOIN

    AD.Cnty ON AD.Addrs.CntyKey = AD.Cnty.CntyKey INNER JOIN

    AD.Dev ON AD.Addrs.DevKey = AD.Dev.DevKey CROSS JOIN

    AD.DevProg CROSS JOIN BD.Morg

  • Phil Parkin

    SSC Guru

    Points: 243474

    As far as I can see, BD.Morg.loan does not appear anywhere in your query. Are you sure about that error message?

    Here is a formatted version of your query, which may be of interest to others:

    SELECT DISTINCT
    AD.Dev.DevName
    ,AD.Cnty.CntyName
    ,BD.Morg.loanNum
    ,BD.Morg.stat
    ,BD.Morg.OPbal
    ,BD.Morg.CPbal
    ,BD.Morg.Ebal
    ,AD.DevProg.loanID
    FROM AD.Addrs
    INNER JOIN AD.Cnty
    ON AD.Addrs.CntyKey = AD.Cnty.CntyKey
    INNER JOIN AD.Dev
    ON AD.Addrs.DevKey = AD.Dev.DevKey
    CROSS JOIN AD.DevProg
    CROSS JOIN BD.Morg;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pdsqsql

    SSC Veteran

    Points: 238

    Thanks for your response.

    with column, it was throwing error when i tried to use CROSS JOIN so i just use schema and database name.

    You can ignore the error as if u can help me to build the query based on

    I am trying to create a view from one DW Database tables to another DW database (which is empty).

    I have total 5 tables - 4 tables in one schema and another table is in another schema from same Source database.

    Morg.loanNum and DevProg.loanID has 1 to many relationship.

  • pdsqsql

    SSC Veteran

    Points: 238

    I ran the query using your formatted query but it's just executing (running almost 7 minutes and still running) as looks like because of the CROSS JOIN, it's doing Cartesan results.

  • Phil Parkin

    SSC Guru

    Points: 243474

    pdsqsql wrote:

    I ran the query using your formatted query but it's just executing (running almost 7 minutes and still running) as looks like because of the CROSS JOIN, it's doing Cartesan results.

    And that's what your query is telling it to do.

    It's difficult to help you without seeing some table definitions, sample data (in the form of INSERT statements) and desired results (based on the sample data provided).

    The fact that the tables are spread across multiple databases is not relevant at this point. Let's get the query working how you want, first.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pdsqsql

    SSC Veteran

    Points: 238

    Thanks Phill for your quick response.

    Is it anyway you can guide to join Multiple tables from two databases?

    I am trying with CROSS JOIN and also LEFT OUTER  JOIN.

    I think Table having ! to Many relationship for Morg.loanNum and DevProg.loanID that causing issue?

  • Phil Parkin

    SSC Guru

    Points: 243474

    To (INNER) join table T1 in database DB1 to table T2, in database DB2, you can use this syntax (assuming you are running the query from the context of DB1)

    SELECT *

    FROM dbo.T1 t1

    JOIN DB2.dbo.T2 t2 on t1.Id = t2.Id

    Whether you choose to change the JOIN to a LEFT JOIN, a CROSS JOIN or a FULL JOIN depends on your own requirements and the nature of the relationships between the tables.

    I cannot see the design of your tables or the data inside them, so I cannot guide you further.

     

     

     

     

     

    • This reply was modified 6 days, 8 hours ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pdsqsql

    SSC Veteran

    Points: 238

    Thanks Phill.

    I think i was able to make the query.

    I was using join in wrong order as i should use Dev as driver table instead of addrs in join conditions. but only issue with this as it's pulling NULL to NULL in both the table column and i wanted to avoid it. How can I avoid it?

    I have built following query:

    SELECT DISTINCT

    D.DevName,

    C.CntyName,

    M.loanNum, M.stat, M.OPbal, M.CPbal,

    BD.Morg.Ebal, DP.loanID

    FROM AD.Dev D

    INNER JOIN AD.DevProg DP ON DP.DevKey = D.DevKey

    INNER JOIN AD.Addrs A ON A.AddrKey = D.PAddrKey

    INNER JOIN AD.Cnty C ON C.CntyKey = A.CntyKey

    INNER JOIN BD.Morg M ON M.LoanNum = DP.LoanID

    but getting like: (See Park Record - which has LoanID and LoanNum both are Empty (NULL) and i don't that record which has NULL for LoanID and LoanNum.

    DevName

    CntyName

    LoanID

    OPbal

    loannum

    Comm

    Ka

    11494

    0

    11494

    Comm

    Ka

    11494

    0

    11494

    Apt

    Ck

    11501

    70000

    11501

    Park

    Ck

    0

    • This reply was modified 5 days, 23 hours ago by  pdsqsql.
  • Phil Parkin

    SSC Guru

    Points: 243474

    I was using join in wrong order as i should use Dev as driver table instead of addrs in join conditions. but only issue with this as it's pulling NULL to NULL in both the table column and i wanted to avoid it. How can I avoid it?

    Please clarify what you mean by 'avoid'. Do you mean that you want to filter these rows out of the results? Or something else?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • pdsqsql

    SSC Veteran

    Points: 238

    Yes Phil,  I wanted to filter out as don't want to show.

    I should try following?

    ISNULL(loanNum, ' ') <> ' ' or RTRIM (LTRIM(LoanNum) <> ' '

  • Phil Parkin

    SSC Guru

    Points: 243474

    If they really are NULL (and not just empty strings), simply add a WHERE clause to your query:

    WHERE NOT (LoanID IsNull and LoanNum is NULL)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply