Sql Server DW - Multiple join

Question

Post reply

Sql Server DW - Multiple join

pdsqsql

Old Hand

Points: 358
More actions
July 9, 2019 at 5:44 pm

#3661281

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

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pdsqsql Old Hand Points: 358 More actions · Answer 2

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 Old Hand Points: 358 More actions · Answer 3

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: 246979 More actions · Answer 4

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pdsqsql Old Hand Points: 358 More actions · Answer 5

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: 246979 More actions · Answer 6

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 5 years, 5 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pdsqsql Old Hand Points: 358 More actions · Answer 7

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 years, 5 months ago by pdsqsql.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pdsqsql Old Hand Points: 358 More actions · Answer 9

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: 246979 More actions · Answer 10

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)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

pdsqsql Old Hand Points: 358 More actions · Answer 11

Thanks Phil.

Thanks for the help!

I think following works

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