Query with inner join (sql)

Question

I have 3 tables:

first table is feeds:

id
tittle
description
image

second table is favorite_feeds:

id
feed_id
user_id

third table is users:

id
user_name

I'm trying to build relation many to many

For example if I want get feeds with where user_id = 4 it is easy, I use query: SELECT feeds INNER JOIN favorite_feeds ON feeds.id = favorite_feeds.id WHERE favorite_feeds.user_id = 4

but it is possible to receive all feed which have and don't have current user(4)

for example:

id--tittle--description--image--user_id
------------------------------

0--tittle1--description1--image1--4
-----------------------------------
1--tittle2--description1--image1--null
-----------------------------------
2--tittle3--description1--image1--null
-----------------------------------
3--tittle4--description1--image1--4
-----------------------------------

user_id = null if feed don't have user

the maximum that I got something like this:

id--tittle--description--image--user_id
------------------------------

0--tittle1--description1--image1--4
-----------------------------------
1--tittle2--description2--image1--3
-----------------------------------
1--tittle3--description2--image1--2
-----------------------------------
2--tittle4--description3--image1--4
-----------------------------------
3--tittle2--description4--image1--2
-----------------------------------
3--tittle3--description4--image1--3
-----------------------------------
3--tittle4--description4--image1--4
-----------------------------------

`WHERE favorite_feeds.user_id = 4 OR favorite_feeds.user_id = 4 IS NULL` — Siyual, Dec 22 '15 at 17:16
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — Sean Lange, Dec 22 '15 at 17:31

score 0 · Accepted Answer · answered Dec 22 '15 at 18:31

Let me build up some data here:

Tables:

create table users (id int, user_name varchar(20));
insert into users values (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

create table feeds (id int, tittle varchar(20), description varchar(20), image varchar(20));
insert into feeds values 
(1, 'title1', 'title1', 'title1'),
(2, 'title2', 'title2', 'title2');

create table favorite_feeds (id int not null auto_increment, feed_id int, user_id int, primary key (id));
insert into favorite_feeds (feed_id, user_id) values 
(1, 1), (1, 2), (1, 4),
(2, 1), (1, 2), (1, 3);

Data

feeds

+------+--------+-------------+--------+
| id   | tittle | description | image  |
+------+--------+-------------+--------+
|    1 | title1 | title1      | title1 |
|    2 | title2 | title2      | title2 |
+------+--------+-------------+--------+

users

+------+-----------+
| id   | user_name |
+------+-----------+
|    1 | a         |
|    2 | b         |
|    3 | c         |
|    4 | d         |
+------+-----------+

favorite_feeds

+----+---------+---------+
| id | feed_id | user_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       1 |       4 |
|  4 |       2 |       1 |
|  5 |       1 |       2 |
|  6 |       1 |       3 |
+----+---------+---------+

Query

select f.*, ff.user_id 
from ( 
  -- find all combinations of feed and userid
  select f.id as fid, u.id as uid 
  from feeds f, users u 
) t 
left join favorite_feeds ff on ff.feed_id = t.fid and ff.user_id = t.uid 
inner join feeds f on f.id = t.fid 
where (ff.user_id is not null and t.uid = 4) 
   or (ff.user_id is null and t.uid = 4);

Result:

+------+--------+-------------+--------+---------+
| id   | tittle | description | image  | user_id |
+------+--------+-------------+--------+---------+
|    1 | title1 | title1      | title1 |       4 |
|    2 | title2 | title2      | title2 |    NULL |
+------+--------+-------------+--------+---------+

Alternate query

select f.*, 4 as user_id
from feeds f 
where exists (
  select 1 from favorite_feeds ff
  where user_id = 4 and feed_id = f.id
)
union all
select f.*, null as user_id
from feeds f 
where NOT exists (
  select 1 from favorite_feeds ff
  where user_id = 4 and feed_id = f.id
)

Example: http://sqlfiddle.com/#!9/bf490/3

Query with inner join (sql)

1 Answers1