how to join multiple tables - 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

General SQL Server Forums

New to SQL Server Programming

how to join multiple tables

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 00:13:28

hi i user this join and i have the answer like this"

select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from [usermaster] u inner join [codeMaster] c
on 'SP'=c.code
where u.userid = '2'

result:
userid user_name password role_code expiry_date
2 billgates bill Supervisor 02/06/2007

created_date active
2007-02-06 00:00:00.000 0

so i have to join one more table which has the following records;

select * from HRUser_developerlog
result:
insserted_id user_date table operation userid
10 1/24/2007 11:47:54 AM usermaster insert 1
1 1/24/2007 1:02:18 PM usermaster insert 1
11 1/25/2007 9:26:12 AM usermaster insert 1
12 2/5/2007 9:56:48 AM usermaster update 1
12 2/5/2007 10:23:01 AM usermaster insert 1
12 2/5/2007 10:23:38 AM usermaster update 1
12 2/5/2007 4:10:11 PM usermaster update 1
2 2/6/2007 8:53:37 AM usermaster insert 1
2 2/6/2007 9:48:24 AM usermaster delete 1

so i need to take the user_date using inserted_id and operation so i need the output as follows:

userid user_name password role_code expiry_date user_date
2 billgates bill Supervisor 02/06/2007 2/6/2007 9:48:24 AM

for this i tried the following query:

select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active,
v.user_date
from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v
on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete'
where u.userid = '2'

but i am getting error.can any onre please help me and please give me query please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-06 : 00:16:54

you got the syntax wrong

it should be


FROM       table1 
INNER JOIN table2 ON  < join condition > 
INNER JOIN table3 ON  < join condition > 
WHERE < where condition >

try again with the above syntax

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-02-06 : 00:24:36

hi khtan thanks for ur cute reply i got it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources