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 * FROM users u
WHERE u.id IN (SELECT f.friend_id
FROM friends f
WHERE f.user_id = 1);
| id | image_width |
|---|---|
| 3 | 520 |
| 4 | 200 |
| 7 | 900 |
| 10 | 610 |
| 13 | 90 |
Select friend_id from friends where user_id = 5;
-- '5' here is User 'B'
| friend_id |
|---|
| 10 |
| 13 |
| 3 |
-- 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