How to get one extra record for LEFT JOIN to represent a record not include on the left joined table

I have a database with two tables one table (shops) has an admin user column and the other a user with less privileges. I plan to LEFT JOIN the table of the user with less privileges. When I retrieve the data, the records for the admin user must be on a separate row and must have NULL values for the left joined table followed by records of users with less privileges (records of the left joined table) if any. I am using MySQL.

I have looked into the UNION commands but I don’t think it can help. Please see the results bellow of what I need.

Thank you.

SELECT * 
    FROM shops LEFT JOIN users USING(shop_id) 
    WHERE shop_id = 1 AND (admin_id = 1 OR user_id = 1);

+---------+----------+---------+
| shop_id | admin_id | user_id |
+---------+----------+---------+
|       1 |        1 |    NULL |    <-- Need this one extra record
|       1 |        1 |       1 |
|       1 |        1 |       2 |
|       1 |        1 |       3 |
+---------+----------+---------+

Here is an example structure of the databases and some sample data:

CREATE SCHEMA test DEFAULT CHARACTER SET utf8 ;
USE test;

CREATE TABLE admin(
    admin_id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(admin_id)
);

CREATE TABLE shops(
    shop_id INT NOT NULL AUTO_INCREMENT,
    admin_id INT NOT NULL,
    PRIMARY KEY(shop_id),
    CONSTRAINT fk_shop_admin FOREIGN KEY(admin_id) REFERENCES admin (admin_id)
);

CREATE TABLE users(
    user_id INT NOT NULL AUTO_INCREMENT,
    shop_id INT NOT NULL,
    CONSTRAINT fk_user_shop FOREIGN KEY(shop_id) REFERENCES admin (shop_id)
);


-- Sample data
INSERT INTO admin() VALUES ();
INSERT INTO shops(admin_id) VALUES (1);
INSERT INTO users(shop_id) VALUES (1),(1),(1);

Answer

I think you need union all:

select s.shop_id, s.admin_id, null as user_id
from shops s
where s.shop_id = 1
union all
select s.shop_id, s.admin_id, u.user_id
from shops s join
     users u
     on s.shop_id = u.shop_id
where shop_id = 1;

Advertisement

Answer