confusing table join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

confusing table join

Author

Topic

macsterling
Yak Posting Veteran

56 Posts

Posted - 2008-09-29 : 23:53:17

When you join tables there is a steping process from one table to the next. - Join table 1 to table 2 where some field is common. If you join a third table, it is joined to the second on some common field. BUT now I need another field on the third table to match a field on the first. How do I do that? The last table has a column that matches the a field in the first (not the joined field), but by the time I get thru the joins there are several rows that make the last match. I need to select the one that has the "matching" field. I can't link directly from table 1 to table 3, because the join is used to translate a value (thru tables 2 and 3).

1 UPDATE CLIENTS <=========== table 1
2 SET SPECID = s.SPECID
3 FROM ClientSpecialties s <=========== table 2
4 JOIN CLIENT_UDF_SPECIALITY u
5 ON s.specname = u.speciality
6 JOIN xfer_client x <=========== table 3
7 ON u.sema4_udf = x.cliud01
8 and clientid = (RTRIM(LTRIM(CLIIDNO)))

This does not work because the clientid in line 8 is the field I need to match in the first table (CLIENTS). I get an error on clientid because it is not in table 2, the "joined' table

If I take line 8 out all the SPECID's are the same - the first one in the xfer table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 00:07:55

Do like below

1 UPDATE c 
2 SET c.SPECID = s.SPECID
3 FROM CLIENTS c
4 JOIN xfer_client x
5 ON x.clientid = (RTRIM(LTRIM(c.CLIIDNO)))
6 JOIN CLIENT_UDF_SPECIALITY u
7 ON u.sema4_udf = x.cliud01
8 JOIN ClientSpecialties s 
9 ON s.specname = u.speciality

macsterling
Yak Posting Veteran

56 Posts

Posted - 2008-09-30 : 00:35:15

Thanks, didn't remember the "UPDATE c .... FROM CLIENTS c" part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources