Sql SERVER DW - Multiple join

Transact-SQL
1

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

2

is this on the same server? or different servers

3

same server

4

what do you get when you do

select top 10 m.loanNum from BD.Morg m

also post back what you see for

select t.name, c.name 
from BD.sys.tables t 
join BD.sys.columns c on t.object_id = c.object_id 
where t.name =  'Morg' and c.name ='loanNum'
5

I will check it out, Thank you!

6

Is it I can use DatabaseName.SchemaName.TableName.ColumnName to select Fields and Data from Source Database tables to another Target Database as it's on the same server?
I am trying to create a View into Target Database using from the Source Database Tables.

7

What was the result of the

what did you find on this first ^^

8

Top 10
5190
5201
5209
5215
5230
5241
5245
5252
5253
5254

And second one retrieving 'Morg' and 'loanNum'

9
10

I see your mesage - ERROR: Msg 4104, Level 16, State 1, Line 31
--The multi-part identifier "BD.Morg.loan" could not be bound.
Can you please clarify What you mean by?

11

is there such a column named load in BD.Morg

12

Sorry it's Loannum

13

so what is the issue?

You originally said
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.

14

Thanks Yosiasz.
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
Sr Com Kan 11494 0 11494
Sr Com Kan 11494 0 11494
Apartments Ck 11501 70000 11501
Park CK 0
15

What data type is LoanId and LoanNum

16

VARCHAR - both the field but one is it self NULLable column and lenght loanID VARCHAR(12)
and LOANNUM VARCHAR(12)

17

Those values are not null. They are empty string. If you need to remove them just filter them out using where rtrim(ltrim(loadnum)) <> ''

Also what is up with your county data, some capital letter some not?

18

Yeah, I can try ISNULL(loannum,'') <> '' , right?

19

Nope. From what you show us on your sample data, that column does not contain null value, visually

20

Thanks for your help!
I tried following and works.

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