Highly confusing as JorgeM has politely pointed out. Are you trying to collect the username from the record that has the user_id of 3? What do you mean recipient? Are you constructing a script in a programming language at the same time too? Get back to us when you have enough time to... type?
sorry guyes. Basically i have two table.one table containe user details and other table containe contact details.
you can see there is two column user_id and to_user. i want a query that get records from these
both tables and let says when user_id 5 login in to site he will see repient as 3 id with name in a form and when 3 login he will see 5 as recepitent.
Ok so if I understand you correctly, when user 5 logs in, you want to run a query and find out who his contact is, which should be "rohit" because in the contact table user 5 has a connection to user 3.. correct?
I dont have access to MySQL at the moment (sorry about that); my query was designed in MSSQL. it should be the same, if not, you should be able to update it accordingly.
SELECT _userDetails.user_id, _userDetails_1.username as [contactUserName]
FROM _contactDetails INNER JOIN
_userDetails ON _contactDetails.user_id = _userDetails.user_id INNER JOIN
_userDetails AS _userDetails_1 ON _contactDetails.to_user = _userDetails_1.user_id
Using the exact table structure you provided....results as shown..
SELECT ud.user_id, ud.username AS contactUserName
FROM add_to_contact_list atcl
INNER JOIN user_detail ud ON atcl.user_id = ud.user_id
INNER JOIN user_detail AS userDetails_1 ON atcl.to_user = userDetails_1.user_id
WHERE atcl.to_user =3 // session user_id return 5 , vimal
when user logged in user_id is 5
WHERE atcl.to_user =5 // return empty result set.