Have an account? Sign in
Login  Register  Facebook
Web Development Problems
another sql query question
I have ONE table. it has 4 columns as following:
I want to perform 2 tasks.

Task 1:
1. First i have a specific user, I want to see with which user it is connected to.
lets say alex in following table. I want to check if alex is connected to another user? In following table it is connected with John and christina, so i select john and christina.

Task2:
Now I have another user. Lets say martin, I want to see which of the previous selected users (john and christina) are connected with the martin.

ID	user1		user2		status
1	 alex		john		   1
2	 john		martin		   1
3	 Jane		smith		   1
4        smith		john               1
5 	 christina	alex		   1
For example, in above table: People who are connected to alex are john and christina. We want to check which of the users (john and christina) are connected with martin. and the result should be row2.

It can be one query or more than one query and use php for loop etc.

Thanks for your reply. Could you please be more specified about the database design? One important thing is that the connection need to work both way, for example, What my idea is: user1 and user2 are connected. If any of the user want to be disconnected then the connection is closed.

Do you mean I should add 2 rows for each connection, like 
ID    user       connected_with
1      alex        john
2     john         alex


Did you mean this? It mean if alex dont want to be any more connected, it can disconnect however john will be still connected to alex. I wanted that if any of the user decide not to be anymore connected, the relationship is broken, and for that purpose i thought previous approach would be better.
Please give me some idea about the database design. Thanks.
(PS. Theres another table "users" which has ID for each user)
Started: September 19, 2011 Latest Activity: September 19, 2011 mysql query select
5 Answers
Thanks everyone for your replies. You all are mentioning that the database design is not good, can you please tell me what is wrong with it. Here is my user table:

Table1
users
ID      username
and here is Table2. user_connections 
ConnectionID     user1_ID     user_Connectedto


Please tell me whats wrong with it and how can i improve?
thanks

Posted: mac
In: September 19, 2011

For your purposes, I would be tempted to say that you should have "double rows for each connection". An example of this would be twitter where personA can follow personB and personB can follow personA but these follow actions are independent of each other.
September 19, 2011

I agree that the code isn't the best, but if you want a query which will grab double links, you'll have to be careful about the direction as mentioned by Marcus. It takes four distinct queries (joined with a union) using the table structure you describe:
SELECT user2 FROM connections
WHERE user1 IN (
    SELECT user2 FROM connections
    WHERE user1 = 'alex'
)
UNION
SELECT user1 FROM connections
WHERE user2 IN (
    SELECT user1 FROM connections
    WHERE user2 = 'alex'
)
UNION 
SELECT user1 FROM connections
WHERE user2 IN (
    SELECT user2 FROM connections
    WHERE user1 = 'alex'
)
AND user1 <> 'alex'
UNION
SELECT user2 FROM connections
WHERE user1 IN (
    SELECT user1 FROM connections
    WHERE user2 = 'alex'
)
AND user2 <> 'alex'

Posted: is_set
In: September 19, 2011

firstly it would indeed be better to structure as jooria suggests...

to find any relationship:
SELECT * from connections where user1 = X OR user2 = X
Replace X with the username you need - or better the userID if you do refactor AS jooria suggests
sorry for the name error

Posted: Go
In: September 19, 2011

My database design is exactly same like what Jooria mentioned in previous post. I have user table which has unique ID for each user. Second table is userconnections, which has user1, user2 and status (1 shows they are connected). Someone merged my post to the original post, can you tell if I should add double rows for each connection? or single is enough? like ID ID_ConnectedTo 1 2 2 1 1 3 3 1
September 19, 2011

I'm not sure your exact requirements but it would seem the database design you chose isn't really the best.

I think you would have wanted to go with a table with unique users. And then another table that links IDs of those users
--Users Table
ID     Name
1      Alex
2      John
3      Jane
...

--UserConnection Table
ID     ID_ConnectedTo
1      2
1      3
In this case, Alex is connected to John and Alex is connected to Jane.

Posted: MacOS
In: September 19, 2011

my database design is exactly like you mentioned. Of course i have users table where each user has a unique ID. UserConnection table has ConnectionID, user1_ID, user2_ID connection_status (1 or 0).
September 19, 2011

That is called a bad database design...At least user2 column should have been an ID of the user1 column.
You need to review your design. This is awful to say the least. I\'d try with one table containing your users, with another table to relate users to one another.

Here\'s what you asked for, for you current schema::
SELECT user2 FROM
(SELECT user1, user2 FROM connections
UNION
SELECT user2, user1 FROM connections) t
WHERE user1 = \'martin\'
You should consider the idea that \"john connected with alex\" isn\'t the same as \"alex connected with john\". If this is something like facebook, where a person has to allow a connection, then you\'ll want to build the connections both ways. If the connection is only in one direction, it means that the connection hasn\'t been approved by the other party yet.

Please review more normalized database designs suggested by other users.

Posted: xtremex
In: September 19, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer