SQLTeam.com | Weblogs | Forums

Return records based on lookup in other tables


Hi There,
I'm in need of your help.

I'm still having trouble working this out.

I have 3 tables.
Table1 Fields = AutoId, Description,ProbId,UserName
Table2 Fields = UserId, UserName
Table3 Fields = AutoId, TypeId, UserId

I need to show all the records in Table1 where Table3.UserId = Table2.UserId and Table3.TypeId = 2

The link between Table1 and Table2 is UserName but I cannot create the query.

Thanks for any help you can give me.

Best Regards,



Select t1. AutoId, t1. Description, t1. ProbId, t1. Username FROM t1
Inner join t2
On t1. Username=t2.username
Inner join t3
On t2. Userid=t3.Userid and t3. Typeid=2

Select	t1.AutoId, t1.Description, t1.ProbId, t1.Username 
FROM	Table1 AS t1
	Inner join Table2 AS t2
		 On t1.Username = t2.username
	Inner join Table3 AS t3
		 On t2.Userid = t3.Userid
		and t3.Typeid = 2

I've just tidied up scarela's answer to remove spaces, in case the O/P tries to cut & paste the answer, and I've also put in different example table names so that the Aliases will work with just the actual table names being replaced.