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.
| Author |
Topic |
|
nbalraj
Starting Member
21 Posts |
 Posted - 2010-08-17 : 00:30:46
|
| User table-----------user_id(pk)first_namephysical_locservice_typeuser affiliated location table------------------------------user_id(pk)(fk)affiliate_loccase table-----------case_id(pk)user_id(fk)service_typeI need to display all the users first_name, whose service_type, physical_loc and afiliate_loc are the same as the logged in user.so the input to the query will be the user_idcan any one give some ideas? |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-17 : 08:27:58
|
| join the tables using user_id--------------------keeping it simple... |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-17 : 08:35:32
|
quote:
Originally posted by nbalraj
User table-----------user_id(pk)first_namephysical_locservice_typeuser affiliated location table------------------------------user_id(pk)(fk)affiliate_loccase table-----------case_id(pk)user_id(fk)service_typeI need to display all the users first_name, whose service_type, physical_loc and afiliate_loc are the same as the logged in user.so the input to the query will be the user_idcan any one give some ideas?
Here's one wayselect b.first_name,b.service_type,c.affiliate_locfrom(select * from User_Table where [user_id] = @user_id) ainner join User_Table b on a.first_name = b.first_name and a.service_type = b.service_type inner join User_Affiliated_Location c on a.affiliate_loc = c.affiliate_loc |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 11:07:26
|
quote:
Originally posted by vijayisonly
quote:
Originally posted by nbalraj
User table-----------user_id(pk)first_namephysical_locservice_typeuser affiliated location table------------------------------user_id(pk)(fk)affiliate_loccase table-----------case_id(pk)user_id(fk)service_typeI need to display all the users first_name, whose service_type, physical_loc and afiliate_loc are the same as the logged in user.so the input to the query will be the user_idcan any one give some ideas?
Here's one wayselect b.first_name,b.service_type,c.affiliate_locfrom(select * from User_Table where [user_id] = @user_id) ainner join User_Table b on a.first_name = b.first_name and a.service_type = b.service_type inner join User_Affiliated_Location c on a.affiliate_loc = c.affiliate_loc
where are you checking for logged in user?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Retrieval by combining 3 tables