By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 5.7.39 |
CREATE TABLE friends(
`id` INTEGER,
`user_id` INTEGER,
`friend_id` INTEGER
);
INSERT INTO friends
(`id`, `user_id`, `friend_id`)
VALUES
('1', '1', '3'),
('2', '1', '4'),
('3', '2', '3'),
('4', '5', '10'),
('5', '1', '10'),
('6', '1', '13'),
('7', '1', '7'),
('8', '2', '9'),
('9', '3', '10'),
('10', '4', '3'),
('11', '5', '13'),
('12', '5', '3');
Records: 12 Duplicates: 0 Warnings: 0
CREATE TABLE users(
`id` INTEGER,
`image_width` INTEGER
);
INSERT INTO users
(`id`, `image_width`)
VALUES
('1', '480'),
('2', '500'),
('3', '520'),
('4', '200'),
('5', '600'),
('6', '800'),
('7', '900'),
('8', '330'),
('9', '450'),
('10', '610'),
('11', '780'),
('12', '820'),
('13', '90');
Records: 13 Duplicates: 0 Warnings: 0
-- My Current Query
-- '1' here is User 'A'
SELECT u.*,
IF(friend_id IS NULL,0,1) amIfollowing
FROM users u
LEFT JOIN (
Select friend_id
from friends
where user_id = 5
) f ON f.friend_id = u.id
WHERE u.id IN (SELECT f.friend_id
FROM friends f
WHERE f.user_id = 1)
ORDER BY u.id
| id | image_width | amIfollowing |
|---|---|---|
| 3 | 520 | 1 |
| 4 | 200 | 0 |
| 7 | 900 | 0 |
| 10 | 610 | 1 |
| 13 | 90 | 1 |
-- Desired Output that I want...
CREATE TABLE DesiredOutput(
`id` INTEGER,
`image_width` INTEGER,
`amIfollowing` INTEGER
);
INSERT INTO DesiredOutput
(`id`, `image_width`, `amIfollowing`)
VALUES
('3', '520', '1'),
('4', '200', '0'),
('7', '900', '0'),
('10', '610', '1'),
('13', '90', '1');
Select * from DesiredOutput;
--Here user_id = '5' is used to check "amIfollowing" column
-- '5' here is User 'B'
Records: 5 Duplicates: 0 Warnings: 0
| id | image_width | amIfollowing |
|---|---|---|
| 3 | 520 | 1 |
| 4 | 200 | 0 |
| 7 | 900 | 0 |
| 10 | 610 | 1 |
| 13 | 90 | 1 |
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--Here user_id = '5' is used to check "amIfollowing" column -- '5' here is User ' at line 1