Return to SQL Subquery

Question

I apologize for the title, but I did not find the best. My problem is this: I want to select all fields from the "log" table, where the "user" field is "user1" or "user2 in" friendships. My query looks like this, but returns a null value:

SELECT * FROM `log` WHERE `user` = ANY(SELECT `user1` FROM `friendships` WHERE (`user1` = 1 OR `user2` = 1) AND `active` = 1) OR `user` = ANY(SELECT `user2` FROM `friendships` WHERE (`user1` = 1 OR `user2` = 1) AND `active` = 1) GROUP BY `arguments` ORDER BY `created` DESC 

If I used only one subquery, it works, but I also need a second field. And there are more values ​​(the reason I use subqueries with ANYONE, not JOIN). I hope someone finds my mistake :).

Answer 1

Hope this works:

 SELECT * FROM `log` INNER JOIN `friendships` on `log`.`user` = `friendships`.`user` WHERE `friendships`.`user` in (1,2) AND `friendships`.`active` = 1 ORDER BY `created` DESC 

PS: Can I publish the schema (or structure) of your table?

UPDATE 1

 SELECT * FROM ( SELECT * FROM `log` INNER `friendships` on `log`.`user` = `friendships`.`user1` WHERE `friendships`.`user1` in (1,2) AND `friendships`.`active` = 1 UNION SELECT * FROM `log` INNER `friendships` on `log`.`user` = `friendships`.`user2` WHERE `friendships`.`user2` in (1,2) AND `friendships`.`active` = 1 ) as iTable ORDER BY iTable.`created` DESC 

Answer 2

I would probably try a different approach, maybe something like this:

 SELECT * FROM `log` WHERE `user` IN ( SELECT `user1` FROM `friendships` WHERE `user2` = @user AND `active` = 1 UNION ALL SELECT `user2` FROM `friendships` WHERE `user1` = @user AND `active` = 1 UNION ALL SELECT @user ) GROUP BY `arguments` ORDER BY `created` DESC 

Although, to be honest, I would not choose columns that were not aggregated and not included in GROUP BY in such a query, although MySQL would allow me to do this.

Another alternative:

 SELECT * FROM `log` WHERE `user` IN ( SELECT CASE `user1` WHEN @user THEN `user2` ELSE `user1` END AS `user` FROM `friendships` WHERE (`user1` = @user OR `user2` = @user) AND `active` = 1 UNION ALL SELECT @user ) GROUP BY `arguments` ORDER BY `created` DESC 

Answer 3

Reinstall OR in one list using union:

 select * from `log` where `user` in ( select user1 from friendships where user1 in (1, 2) and active) union select user2 from friendships where `user1` in (1, 2) and active ) group by arguments order by `created` desc 

or use join in standard join

 select * from ( select l.* from `log` l join friendships f on l.user = f.user1 and f.user1 in (1, 2) and f.active union select l.* from `log` l join friendships f on l.user = f.user2 and f.user2 in (1, 2) and f.active ) x group by arguments order by `created` desc