Link to home
Start Free TrialLog in
Avatar of Julio Jose
Julio JoseFlag for Malaysia

asked on

MSSQL join different row from other table

https://www.experts-exchange.com/questions/29012433/sql-query-display-the-latest-row.html

This is the follow up question for existing answered question.

I have Table2 and Table3, I need to match Table2 GID to Table3 GID and Table2 IPADDR match Table1 ADDR,

Table3 row name LH,CH,GT,YT,RT  join to Table1 query display as one table.

Table2
GID, IPADDR

Table3
GID,LH,CH,GT,YT,RT

Table 1
select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc, E_DT desc) idx
 from C_VW
where E_D= 'RT - M'
) a
where idx = 1
ORDER BY PD DESC, E_DT;

Open in new window

Avatar of yo_bee
yo_bee
Flag of United States of America image

I would build off the Cartesian join and I would build your select with the tablename.columnName

select NAME,ADDR,ID,E_DT,P_DT,PD from
(
select a.NAME,a.ADDR,a.ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc, E_DT desc) idx
 from C_VW
where E_D= 'RT - M'
) a inner join Table2 as t2 on a.addr = t2.addr inner join table3 as t3 on t2.GID = t3.GID
where idx = 1
ORDER BY PD DESC, E_DT;

Open in new window

is this something u look for?

select
c.LH, c.CH, c.GT, c.YT, c.RT 
from Table3 c inner join Table2 b
on c.GID = b.GID
inner join Table1 a on b.IPADDR = a.ADDR
Avatar of Julio Jose

ASKER

Clarify

I need a query that can display the row on the result

NAME,ADDR,ID,E_DT,P_DT,PD - table1 LH,CH,GT,YT,RT - table3
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry I think what yo_bee provided should work.
I made a mistake and added the a.tablename within the Cartesian and not on the outer part of the select part of the statement.

When you build the select part that is outside the Cartesian you need to add your table columns you wish to display.
I tried yo_bee query but fail

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "a.NAME" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "a.ADDR" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "a.GID" could not be bound.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'ADDR'.

and I need confirmation to correct the t2.ipaddr

) a inner join Table2 as t2 on a.addr = t2.ipaddr inner join table3 as t3 on t2.GID = t3.GID
You need to change the name since you have Name in multiple tables

What you can do is

Select a.Name  as Name1, t1.name as Name2   and so on

You only have to do this if you have the same column name

Can you post your full statement?
Hi yoo_bee, I don't have same column name, paste the table / column name again

Table1
NAME,ADDR,ID,E_DT,P_DT,PD

Table2
GID, IPADDR

Table3
GID,LH,CH,GT,YT,RT
Please post your entire sql statement so we all can see where there error is generated from.

If you copied my code I think I know where the issue is.  I reposted your statement and added the table name within the Cartesian part and the table name a. was not even created.
Try this.

select NAME,ADDR,ID,E_DT,P_DT,PD,LH,CH,GT,YT,RT  from
(
select NAME,ADDR,ID,E_DT,P_DT,PD,
row_number() over (partition by NAME order by PD desc, E_DT desc) idx
 from C_VW
where E_D= 'RT - M'
) a inner join Table2 as t2 on a.addr = t2.addr inner join table3 as t3 on t2.GID = t3.GID
where idx = 1
ORDER BY PD DESC, E_DT
yea, generally yo_bee's comment in ID: 42075336 has same concept how to generate the output as mentioned in my comment earlier before.

see if both are working fine for you?
@yoo_bee, receive this error

Msg 207, Level 16, State 1, Line 3
Invalid column name 'GID'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'ADDR'.

@Ryan
Your query is work, I still checking the output result
Ryan solution work for me, thanks
Sorry I was not able to help. I am not sure why mine did not work.  It does not really matter the order of you place the inner join with the Cartesian and the other tables.  

I wish you would have posted your entire sql statement so we could have isolated your syntax error.  
If you are going to be doing more queries like this you will need to figure out how to build the queries and resolve your syntax errors.