SQLTeam.com | Weblogs | Forums

Return records based on lookup in other tables


#1

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,

Steve.


#2

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


#3
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.