Julio Jose
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
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;
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
When you build the select part that is outside the Cartesian you need to add your table columns you wish to display.
ASKER
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
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?
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?
ASKER
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
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,Y T,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
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,
(
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?
see if both are working fine for you?
ASKER
@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
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
ASKER
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.
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.
Open in new window