LEFT JOIN with two tables

Question

I have 3 tables and want to select all records from "sales" with clients id according to their districts.

create table t_sales (
   id                   SERIAL               not null,
   parent               INT4                 null,
   client1              TEXT                 null,
   client2              TEXT                 null,
   constraint PK_T_SALES primary key (id)
);
create table t_client (
   id                   SERIAL               not null,
   name                 TEXT                 null,
   district             INT4                 null,
   constraint PK_T_CLIENT primary key (id)
);
create table t_sale_district (
   id                   SERIAL               not null,
   sale_id              INT4                 null,
   district_id          INT4                 null,
   constraint PK_T_SALE_DISTRICT primary key (id)
);
INSERT INTO t_client (id,name,district) VALUES(1,'JOHN',1);
INSERT INTO t_client (id,name,district) VALUES(2,'JOHN',2);
INSERT INTO t_client (id,name,district) VALUES(3,'john',1);
INSERT INTO t_client (id,name,district) VALUES(4,'john',2);
INSERT INTO t_client (id,name,district) VALUES(5,'MAX',1);
INSERT INTO t_client (id,name,district) VALUES(6,'MAX',2);
INSERT INTO t_client (id,name,district) VALUES(7,'max',1);
INSERT INTO t_client (id,name,district) VALUES(8,'max',2);

INSERT INTO t_sales (id,parent,client1,client2) VALUES (1,1,'JOHN','john');
INSERT INTO t_sales (id,parent,client1,client2) VALUES (2,1,'JOHN','MAX');

INSERT INTO t_sale_district (id,sale_id,district_id) VALUES (1,1,1);
INSERT INTO t_sale_district (id,sale_id,district_id) VALUES(2,1,5);

INSERT INTO t_sales (id,parent,client1,client2) VALUES (3,1,'JOHN',NULL);
INSERT INTO t_sale_district (id,sale_id,district_id) VALUES (3,2,2);

This query select as I want but without nulls values:

SELECT s.id,s.client1 as c1,c1.district as d1,s.client2 as c2,c2.district as d2
FROM public.t_sales s
    JOIN t_client c1 ON c1.name=s.client1 
    JOIN t_client c2 ON c2.name=s.client2
    JOIN t_sale_district sd1 ON sd1.district_id = c1.district 
    JOIN t_sale_district sd2 ON sd2.district_id = c2.district 
    WHERE sd1.sale_id=s.id AND sd2.sale_id=s.id

1;"JOHN";1;"john";1
2;"JOHN";2;"MAX";2

But I want to add null values:

  1;"JOHN";1;"john";1
    2;"JOHN";2;"MAX";2
    3;"JOHN";2;NULL;NULL

Is it available to make such query with null values in sales?

PS I know about LEFT JOIN:

SELECT s.id,s.client1 as c1,c1.district as d1,s.client2 as c2,c2.district as d2
FROM public.t_sales s
LEFT JOIN t_client c1 ON c1.name=s.client1 
LEFT JOIN t_client c2 ON c2.name=s.client2
LEFT JOIN t_sale_district sd1 ON sd1.district_id = c1.district AND sd1.sale_id=s.id
LEFT JOIN t_sale_district sd2 ON sd2.district_id = c2.district AND sd2.sale_id=s.id

Output:

1;"JOHN";2;"john";1
1;"JOHN";1;"john";1
2;"JOHN";1;"MAX";2
2;"JOHN";2;"MAX";2
2;"JOHN";1;"MAX";1
2;"JOHN";2;"MAX";1
1;"JOHN";2;"john";2
1;"JOHN";1;"john";2
3;"JOHN";2;"";
3;"JOHN";1;"";

PS2. I found only one solution (thanx Zaynul Abadin Tuhin):

select ids,t1.* from    
(
    select s.id as ids,c1.district as d1, c2.district as d2,c1.id as c1,c2.id as c2,s.client1,s.client2 from  t_sales s    
     left join t_client c1 on s.client1=c1.name
     left join t_client c2 on s.client2=c2.name
 )
    as t1 
    left join    t_sale_district d1 on d1.sale_id=t1.ids 
    left join    t_sale_district d2 on d2.sale_id=t1.ids 
where
    (t1.d2 IS NULL OR t1.d2=d2.district_id )
    and (t1.d1 IS NULL OR t1.d1=d1.district_id)

Output:

1;1;1;1;1;3;"JOHN";"john"
2;2;2;2;2;6;"JOHN";"MAX"
3;3;1;;1;;"JOHN";""

Is there a solution easier?

left joins should fix this, like you mentioned in the title, but you havent used them in your code? — WhatsThePoint, Sep 19 '18 at 09:47
Welcome to StackOverflow! See [here](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join/16598900#16598900) for more information about the different types of joins. — MBijen, Sep 19 '18 at 09:49
Sure I know about left join, but cant select all records from sales without all records from clients. — Ilya Filatov, Sep 19 '18 at 09:51
Could you edit your question and remove redundant RDBMS tag? You probably use either MySQL or PostgreSQL, not both of them. — Evaldas Buinauskas, Sep 19 '18 at 10:31

Zaynul Abadin Tuhin · Answer 1 · 2018-09-19T13:12:54.567

1

use left join

with t1 as 
(
(select s.id,s.client1 as c1,s.client2 as c2,c.district from  t_sales s

 join t_client c on s.id=c.id

 )
 ) ,t2 as

 (
select * from t1 left join  t_sale_district d on t1.district=d.district_id
 )  select * from t2

For mysql

    select * from
    (
     select * from  t_sales s    
     join t_client c on s.id=c.id
    ) as t1 left join
     t_sale_district d on t1.district=d.district_id

edited Sep 19 '18 at 13:12

answered Sep 19 '18 at 09:45

Zaynul Abadin Tuhin

31,407
5
33
63

No, it includes all records from all tables. – Ilya Filatov Sep 19 '18 at 09:49
@IlyaFilatov check now please – Zaynul Abadin Tuhin Sep 19 '18 at 09:58
Yes, it's work for Postgresql, have you idea for MySql? – Ilya Filatov Sep 19 '18 at 10:10
Db which data I want to export is MySQL – Ilya Filatov Sep 19 '18 at 10:14
that means your db is not postgrey this query required for mysql? – Zaynul Abadin Tuhin Sep 19 '18 at 10:15
Yes, I need query for MySQL – Ilya Filatov Sep 19 '18 at 10:30
@IlyaFilatov added in answer check please and as it solved your problem so accept please – Zaynul Abadin Tuhin Sep 19 '18 at 10:33
Not correct:1;1;"JOHN";"john";1;"JOHN";1;1;1;1 2;1;"JOHN";"MAX";2;"JOHN";2;2;1;5 3;1;"JOHN";"";3;john;1;1;1;1' – Ilya Filatov Sep 19 '18 at 10:39
@IlyaFilatov any issue ? – Zaynul Abadin Tuhin Sep 19 '18 at 10:40
@IlyaFilatov if this query produce in postgrey correct result then definitely it should produce correct in mysql cause no difference between 2 – Zaynul Abadin Tuhin Sep 19 '18 at 10:44
How to know both clients district and id? – Ilya Filatov Sep 19 '18 at 12:53
@IlyaFilatov 1st join client will client id and then with join district table that will return district id – Zaynul Abadin Tuhin Sep 19 '18 at 13:02
@IlyaFilatov check answer again please i missed join key with district table – Zaynul Abadin Tuhin Sep 19 '18 at 13:13
I edit my post with solition - may be you can make it easier? – Ilya Filatov Sep 19 '18 at 14:09

score 1 · Answer 2 · answered Sep 19 '18 at 09:56

You can try with this Query Which contain exactly same result which you needed

SELECT s.id,s.client1 as c1,c1.district as d1,s.client2 as c2,c2.district as d2
FROM public.t_sales s
LEFT JOIN t_client c1 ON c1.name=s.client1 
LEFT JOIN t_client c2 ON c2.name=s.client2
LEFT JOIN t_sale_district sd1 ON sd1.district_id = c1.district AND sd1.sale_id=s.id
LEFT JOIN t_sale_district sd2 ON sd2.district_id = c2.district AND sd2.sale_id=s.id

score 0 · Answer 3 · answered Sep 19 '18 at 13:32

0

try this

SELECT s.id,s.client1 as c1,c1.district as d1,s.client2 as c2,c2.district as d2
FROM public.t_sales s
     left outer JOIN t_client c1 ON c1.name=s.client1 
    left outer JOIN t_client c2 ON c2.name=s.client2

working example here

answered Sep 19 '18 at 13:32

Saif

2,611
3
17
37

score -1 · Answer 4 · answered Sep 19 '18 at 10:02

-1

You can try this SQL

SELECT s.id,s.client1 as c1,c1.district as d1,s.client2 as c2,c2.district as d2
FROM t_sales s
    LEFT JOIN t_client c1 ON c1.name=s.client1 
    LEFT JOIN t_client c2 ON c2.name=s.client2
    LEFT JOIN t_sale_district sd1 ON sd1.district_id = c1.district 
    LEFT JOIN t_sale_district sd2 ON sd2.district_id = c2.district 
    WHERE (sd1.sale_id=s.id AND sd2.sale_id=s.id) OR (sd1.sale_id is null OR sd2.sale_id is null)

answered Sep 19 '18 at 10:02

Nguyên Ngô Duy

348
1
7

Its not correct: 1;"JOHN";1;"john";1 3;"JOHN";1;""; 2;"JOHN";2;"MAX";2 3;"JOHN";2;""; – Ilya Filatov Sep 19 '18 at 10:28

LEFT JOIN with two tables

4 Answers4