LEFT OUTER JOIN query not contain results from left table

Question

i have some tables:

CREATE TABLE IF NOT EXISTS
        days(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        active BOOLEAN
        );

INSERT into days (name, active) VALUES ('S', True);
INSERT into days (name, active) VALUES ('M', True);
INSERT into days (name, active) VALUES ('T', True);
INSERT into days (name, active) VALUES ('W', True);
INSERT into days (name, active) VALUES ('Th', True);
INSERT into days (name, active) VALUES ('Fr', True);
INSERT into days (name, active) VALUES ('Sut', True);

CREATE TABLE IF NOT EXISTS
        user(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mail TEXT,
        nickname TEXT,
        isadmin INTEGER
        );

INSERT into user (nickname, mail) VALUES ('overblown', 'abc@abc.com');


CREATE TABLE IF NOT EXISTS
        dish(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        price FLOAT
        );

INSERT into dish (name,price) VALUES ('Pizza', 40);
INSERT into dish (name,price) VALUES ('Soup', 20);
INSERT into dish (name,price) VALUES ('Bread', 40);
INSERT into dish (name,price) VALUES ('Chips', 42);


CREATE TABLE IF NOT EXISTS
        menu(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        dish_id INTEGER,
        day_id INTEGER
        );

INSERT into menu (dish_id, day_id) VALUES (1, 1);
INSERT into menu (dish_id, day_id) VALUES (2, 1);
INSERT into menu (dish_id, day_id) VALUES (3, 1);
INSERT into menu (dish_id, day_id) VALUES (4, 1);


CREATE TABLE IF NOT EXISTS
        request(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        menu_id INTEGER,
        user_id INTEGER,
        count FLOAT
        );

INSERT into request (menu_id, user_id, count) VALUES (1, 1, 0.5);
INSERT into request (menu_id, user_id, count) VALUES (2, 1, 0.5);

I want to get all the records from the request table for the someone user, but also add to them all the records from the menu table for a someone day, so that the result would be unique relative to the menu_id. Sqlite dont support RIGHT OUTER JOIN, and i trying use UNION:

select user.nickname as nick, dish.name as dish, days.name as day, request.count as size from request 
        INNER JOIN user on user.id = request.user_id 
        INNER JOIN menu on menu.id = request.menu_id
        INNER JOIN dish on dish.id = menu.dish_id
        INNER JOIN days on days.id = menu.day_id
        where user_id=1 and days.id=1
UNION
SELECT null as nick, dish.name as dish, days.name as day, 0.0 size from menu
        INNER JOIN dish on dish.id = menu.dish_id
        INNER JOIN days on days.id = menu.day_id
        where menu.day_id=1;

Seems like good, but result has not unique values. okey, i try used LEFT OUTER JOIN (with changing the sides of the table relative to the operator)

SELECT user.nickname as nick, dish.name as dish, days.name as day, count
        FROM menu
        LEFT outer JOIN request ON request.menu_id=menu.id
        INNER JOIN user on user.id = request.user_id 
        INNER JOIN dish on dish.id = menu.dish_id
        INNER JOIN days on days.id = menu.day_id
        where menu.day_id=1 and request.user_id=1

but result has row only from request tables, and not containt rows from menu. What i does wrong?

expected result:

('overblown', 'Pizza', 'S', 0.5)
('overblown', 'Soup', 'S', 0.5)
('overblown', 'Bread', 'S', NULL)
('overblown', 'Chips', 'S', NULL)

`INNER JOIN` **after** `OUTER JOIN` removes NULL values that the inner join created. — PM 77-1, Apr 17 '19 at 17:46
x RIGHT JOIN y ON c = y LEFT JOIN x ON c. Learn what LEFT/RIGHT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE that requires a right/left [sic] table column to be not NULL after a LEFT/RIGHT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Apr 18 '19 at 07:52
Simulating right join in SQLite is an obvious faq. MIssing nulls after left join is an obvious faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 18 '19 at 07:55
Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Apr 18 '19 at 07:56
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Apr 18 '19 at 07:57

score 1 · Answer 1 · answered Apr 17 '19 at 18:31

You need to use left join throughout the query:

SELECT u.nickname as nick, d.name as dish, dy.name as day, count
FROM request r LEFT JOIN
     menu m
     ON r.menu_id = m.menu_id AND
        m.day_id = 1 LEFT JOIN
     user u
     ON u.id = r.user_id LEFT JOIN
     dish d
     ON d.id = m.dish_id LEFT JOIN
     days dy
     ON dy.id = m.day_id
WHERE r.user_id = 1;

LEFT OUTER JOIN query not contain results from left table

1 Answers1