February 21, 2008 at 2:01 am
I have two tables namely
1)users(uid,name,mailid)and
2)blocked_id(uid,blocked_id)
I want to list out the names of the user and the blocked user
how to write query for this
I wrote this and it is displaying result but I want to know usernames for blocked id users:
SELECT U.uid,U.name,U.mail,B.blocked_id
FROM users U
INNER JOIN block_user B
ON U.uid=B.uid;
User table:
Uid name mailid
1 a a@gmail.com
2 b b@gmail.com
3 c c@gmail.com
4 d d@gmail.com
Block_user
uid blocked_id
12
34
24
31
User name is present only in user table
Now I want to list the names of the blocked user along with the user who have blocked them.
I am able to list only blocked users id but I want their names..
Can anyone help me please...
Thanks in advance
February 21, 2008 at 2:44 am
Join against the user table twice.
Once on the blocked id and once on the userid that has done the block.
That way you can get at both usernames, one from each instance of the usertable.
/Kenneth
February 21, 2008 at 3:27 am
Hi Kenneth
Thanks for your answer
Stil I am unable to get the names of the blocked user..
I am able to retrieve their id's alone...
I want their name also along with the id's
Could you please help me...
February 21, 2008 at 4:46 am
You didn't post what you subsequently tried after the previous (correct) suggestion. I admit that I had no idea what you were on about but the previous suggestion, turned in to SQL, is something like
SELECT U.uid,U.name,U.mail,B.blocked_id, UB.name
FROM users U
INNER JOIN block_user B
ON U.uid=B.uid
INNER JOIN users UB
ON B.blocked_id = UB.uid
You are using the users table twice in the query - once aliased as U and the next time as UB. They contain the same data but are considered as two distinct tables for the purposes of the query. If you want users whether there are blocks or not then you need to consider LEFT, RIGHT or FULL outer joins. Look up "table joins" on this site for some articles.
February 21, 2008 at 4:47 am
Oh, and look up "self joins" too 🙂
February 21, 2008 at 5:08 am
Thanks a lot Ian
It works perfect!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply