Tenho certeza de que ficarei envergonhado com a resposta final, mas estive pesquisando e batendo minha cabeça contra a parede e não consigo descobrir.
Eu tenho quatro tabelas descritas abaixo. Estou tentando obter a consulta que mostra todos os produtos relacionados aos usuários (através da user_productstabela) E todas as categorias.
Definições de tabela
CREATE TEMPORARY TABLE categories (
id INT PRIMARY KEY NOT NULL
, cat_name TEXT
);
CREATE TEMPORARY TABLE products (
id INT PRIMARY KEY NOT NULL
, category_id INT
, prod_name TEXT
);
CREATE TEMPORARY TABLE users (
id INT PRIMARY KEY NOT NULL
, user_name TEXT
);
CREATE TEMPORARY TABLE user_products (
id INT PRIMARY KEY NOT NULL
, product_id INT
, user_id INT
, quantity INT
);
instruções INSERT
INSERT INTO categories VALUES (1, 'cat1')
,(2, 'cat2')
,(3, 'cat3')
,(4, 'cat4');
INSERT INTO products VALUES (1, 1, 'prod1')
,(2, 1, 'prod2')
,(3, 2, 'prod3')
,(4, 3, 'prod4')
,(5, 4, 'prod4');
INSERT INTO users VALUES (1, 'user1')
,(2, 'user2')
,(3, 'user3');
INSERT INTO user_products VALUES (1, 1, 1, 4)
,(2, 2, 1, 3)
,(3, 3, 1, 3)
,(4, 2, 2, 3)
,(5, 2, 2, 2)
,(6, 5, 2, 2);
Eu tentei várias opções, e isso certamente parece um cenário de junção externa. A consulta mais promissora QUE NÃO FUNCIONA é:
SELECT up.*, p.*, c.*, u.*
FROM user_products AS up
JOIN users AS u ON u.id = up.user_id
JOIN products AS p ON p.id = up.product_id
RIGHT OUTER JOIN categories AS c ON c.id = p.category_id
WHERE u.id = 1 OR u.id IS NULL
Resultado da consulta acima
id | product_id | user_id | quantity | id | category_id | prod_name | id | cat_name | id | user_name
----+------------+---------+----------+----+-------------+-----------+----+----------+----+-----------
1 | 1 | 1 | 4 | 1 | 1 | prod1 | 1 | cat1 | 1 | user1
2 | 2 | 1 | 3 | 2 | 1 | prod2 | 1 | cat1 | 1 | user1
3 | 3 | 1 | 3 | 3 | 2 | prod3 | 2 | cat2 | 1 | user1
| | | | | | | 3 | cat3 | |
A razão pela qual não funciona é que ele não retorna 'cat4' da tabela de categorias. Obviamente, isso ocorre porque a WHEREcláusula está selecionando-o do conjunto de resultados. Não consigo obter um conjunto de resultados que inclua todas as categorias + os detalhes dos produtos e quantidades para os quais existem registros na tabela user_products especificada.
A consulta deve
- retornar todas as categorias
- dados de retorno preenchidos para informações do usuário se o usuário tiver esse produto (existe um registro em user_products)
- não retornar linhas com outros IDs de usuário
