I have been trying to get a sub query to work but I'm thinking it needs to be join instead but I'm new to databases and having a hard time wrapping my head around just how it works.
I need to collect a rows from userprofile based on three columns in connect, what I have that doesn't work is this.
SELECT * FROM userprofile WHERE user_id IN
(SELECT connect.user_id, connect.connect_to_id FROM connect WHERE
(user_id = '1' OR connect_to_id = '1') AND is_connected = '1' );
#1241 - Operand should contain 1 column(s)
SELECT u.* FROM userprofile u
JOIN connect c ON (u.user_id = c.user_id OR u.user_id = c.connect_to_id) WHERE
((c.user_id = '1' OR c.connect_to_id = '1') AND c.is_connected = '1') AND u.user_id != '1'
GROUP BY u.id;
SELECT u.* FROM userprofile u WHERE u.user_id IN
(SELECT connect.user_id FROM connect WHERE connect.connect_to_id = '1' AND connect.is_connected = '1'
SELECT connect.connect_to_id FROM connect WHERE connect.user_id = '1' AND connect.is_connected = '1')
It can be done either via a
join or a sub-query. Typically with MySQL joins are more efficient because in many cases MySQL is unable to use indexes with sub-queries. So, in your case the query with a join would be:
SELECT u.* FROM userprofile u JOIN connect c ON (u.user_id = c.user_id OR u.user_id = c.connect_to_id) WHERE (c.user_id = '1' OR c.connect_to_id = '1') AND c.is_connected = '1'