SQL Select Statement with multiple INNER JOINS and WHERE conditions

Question

I already asked this question before. But I didn't provide enough details so I try another attempt :). I have to admit this mistake. First of all, here are my tables:

tbl_user

id         name
1          Tom
2          Cedric (selection with his user)

tbl_store

id         store_name
1          Zurich
2          Bern
3          Luzern

tbl_user_store

id         fk_user         fk_store
1          1               1
2          1               2
3          1               3
4          2               1
5          2               3

tbl_user_entries

id         article_name    fk_user      fk_article      fk_store
1          article4        1                            1
2                          1            1               3
2                          1            2               2

tbl_article

id         article_name
1          article1
2          article2
3          article3

I try to select:

name (from tbl_user, foreign key in tbl_user_entries)
article_name (from tbl_user_entries, if column article_name null then its connected with the foreign key to fk_article)
store_name (from tbl_user, foreign key in tbl_user_entries)

me (inner join, because one user can have multiple stores assigned)

This should only see someone with the some stores assigned. So User Cedric should see:

Expected output:

name         article_name       store_name
Tom          article4           Zurich
Tom          article1           Luzern

Here is the sql code:

CREATE DATABASE debug;
USE debug;

CREATE TABLE tbl_user (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE tbl_store (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  store VARCHAR(255) NOT NULL
);

CREATE TABLE tbl_user_store (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fk_user INT NOT NULL,
  fk_store INT NOT NULL,
  FOREIGN KEY (fk_user) REFERENCES tbl_user(id),
  FOREIGN KEY (fk_store) REFERENCES tbl_store(id)
);

CREATE TABLE tbl_article (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  article_name VARCHAR(255) NOT NULL
);

CREATE TABLE tbl_user_entries (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  article_name VARCHAR(255) DEFAULT NULL,

  fk_user INT NOT NULL,
  fk_article INT,
  fk_store INT NOT NULL,
  FOREIGN KEY (fk_user) REFERENCES tbl_user(id),
  FOREIGN KEY (fk_article) REFERENCES tbl_article(id),
  FOREIGN KEY (fk_store) REFERENCES tbl_store(id)
);

INSERT INTO tbl_user (name) VALUES ('Tom'), ('Cendric');
INSERT INTO tbl_store (store) VALUES ('Zurich'), ('Bern'), ('Luzern');
INSERT INTO tbl_user_store (fk_user, fk_store) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 3);
INSERT INTO tbl_article (article_name) VALUES ('article1'), ('article2'), ('article3');
INSERT INTO tbl_user_entries (article_name, fk_user, fk_article, fk_store) VALUES ('article4', 1, NULL, 1), (NULL, 1, 1, 3), (NULL, 1, 2, 2);

user_1144035 · Answer

If I understand correctly, this is a bunch of joins and some filtering.

The trick is getting the article from either tbl_user_entries or tbl_article. A left join handles that:

with us as (
      select us.fk_user, us.fk_store, u.name, s.store
      from tbl_user u join
           tbl_user_store us
           on us.fk_user = u.id join
           tbl_store s
           on s.id = us.fk_store
     )
select us.*, coalesce(ue.article_name, a.article_name)
from us join
     tbl_user_entries ue
     on us.fk_store = ue.fk_store and
        us.fk_user = ue.fk_user left join
     tbl_article a
     on ue.fk_article = a.id 
where us.store in (select us.store from us where name = 'Cendric') and
      us.name <> 'Cendric';

Here is a db<>fiddle.

user_10498828 · Answer

You need multiple joins of all the tables like this:

select u.name,
       coalesce(ue.article_name, a.article_name) article_name,
       s.store store_name
from tbl_user mu 
inner join tbl_user_store mus on mus.fk_user = mu.id
inner join tbl_user_store us on us.fk_store = mus.fk_store
inner join tbl_user u on u.id = us.fk_user 
inner join tbl_store s on s.id = us.fk_store
left join tbl_user_entries ue on ue.fk_user = u.id and ue.fk_store = s.id  
left join tbl_article a on a.id = ue.fk_article
where mu.name = 'Cendric' and coalesce(ue.article_name, a.article_name) is not null

See the demo.
Results:

> name | article_name | store_name
> :--- | :----------- | :---------
> Tom  | article4     | Zurich    
> Tom  | article1     | Luzern

SQL Select Statement with multiple INNER JOINS and WHERE conditions

2 Answers