Help with some SQL Joins

Well, imagine you have 3 tables (legacy system, cant be changed) and you need to get a list that has all the cities and the sales on each city for a specific employe id. The sales table will not have a record for a city if the employe had never made a sell there.

With some joins I can get the relevant data, but not the complete list with all the cities. I have this working putting the results of a SELECT * FROM city in a list and then running the SQL wuth joins to fill the sales column:

SELECT city.id, city.name, sales.sales, employe.id
FROM city
LEFT JOIN sales ON city.id = sales.cityFK
LEFT JOIN employe ON sales.employeFK = employe.id
WHERE employe.id = 10

What will be the best practice to get the “Desired Result” table directly with SQL?

Thanks

I think you can just change your WHERE to:

employe.id IS NULL OR employe.id = 10

You’ll have to adjust SELECT too so a NULL in sales reports 0 instead.

I tried that, but I think that the Joins don´t return rows where the employe.id is null, so it is the same result.

What flavor of SQL? SQLite, Postgres, something else?

Because in SQLite this gives me the results you’re looking for:

begin;

create table city (
  id integer not null primary key,
  name text not null
);

insert into city (id, name) values 
(1, 'City A'),
(2, 'City B'),
(3, 'City C'),
(4, 'City D'),
(5, 'City E')
;

create table employe (
  id integer not null primary key,
  name text not null
);

insert into employe (id, name) values
(10, 'AAA'), 
(11, 'BBB'), 
(12, 'CCC'), 
(13, 'DDD'),
(14, 'EEE')
;

create table sales (
  cityfk integer not null,
  employefk integer not null,
  sales integer not null
);

insert into sales values
  (1, 10, 5),
  (1, 12, 4),
  (1, 13, 3),
  (2, 10, 9),
  (2, 13, 8),
  (2, 14, 2),
  (4, 10, 4),
  (4, 11, 9),
  (4, 14, 2)
;


SELECT city.id, city.name, iif(sales.sales is null, 0, sales.sales), employe.id
FROM city
LEFT JOIN sales ON city.id = sales.cityFK
LEFT JOIN employe ON sales.employeFK = employe.id
WHERE employe.id = 10 or employe.id is null
;

rollback;

MySQL

Works for me there too, except I don’t know that the equivalent of IIF is.

if you create a fiddle will be easier to help you

https://www.db-fiddle.com/

2 Likes

That’s fantastic!

Schema (MySQL v5.7)

create table city (
  id integer not null primary key,
  name text not null
);

insert into city (id, name) values 
(1, 'City A'),
(2, 'City B'),
(3, 'City C'),
(4, 'City D'),
(5, 'City E')
;

create table employe (
  id integer not null primary key,
  name text not null
);

insert into employe (id, name) values
(10, 'AAA'), 
(11, 'BBB'), 
(12, 'CCC'), 
(13, 'DDD'),
(14, 'EEE')
;

create table sales (
  cityfk integer not null,
  employefk integer not null,
  sales integer not null
);

insert into sales values
  (1, 10, 5),
  (1, 12, 4),
  (1, 13, 3),
  (2, 10, 9),
  (2, 13, 8),
  (2, 14, 2),
  (4, 10, 4),
  (4, 11, 9),
  (4, 14, 2)
;

Query #1

SELECT city.id, city.name, sales.sales, employe.id
FROM city
LEFT JOIN sales ON city.id = sales.cityFK
LEFT JOIN employe ON sales.employeFK = employe.id
WHERE employe.id = 10 or employe.id is null
;
id name sales id
1 City A 5 10
2 City B 9 10
4 City D 4 10
3 City C
5 City E

View on DB Fiddle

You could use Case to get a Zero:

SELECT city.id, city.name, 
(CASE WHEN (sales.sales is null) 
 THEN
      0 
 ELSE
      sales.sales 
 END)
 as sales,
employe.id
FROM city
LEFT JOIN sales ON city.id = sales.cityFK
LEFT JOIN employe ON sales.employeFK = employe.id
WHERE employe.id = 10 or employe.id is null
;
1 Like