I have three tables peoples, travels and hotel_books.
CREATE TABLE peoples (
pe_id varchar PRIMARY KEY,
name varchar
);
CREATE TABLE travels (
tr_id varchar PRIMARY KEY,
owner_id varchar,
town varchar
);
CREATE TABLE hotel_books (
hb_id varchar PRIMARY KEY,
pe_id varchar,
tr_id varchar,
hotel varchar
);
insert into peoples values('pe1', 'Nike');
insert into peoples values('pe2', 'Mike');
insert into peoples values('pe3', 'Dike');
insert into peoples values('pe4', 'Zike');
insert into peoples values('pe5', 'Pike');
insert into travels values('tr1', 'pe1', 'Paris');
insert into travels values('tr2', 'pe2', 'Bern');
insert into travels values('tr3', 'pe3', 'Madrid');
insert into travels values('tr4', 'pe4', 'NY');
insert into travels values('tr5', 'pe5', 'LA');
insert into travels values('tr6', 'pe1', 'London');
insert into travels values('tr7', 'pe1', 'Berlin');
insert into travels values('tr8', 'pe2', 'Rio');
insert into travels values('tr9', 'pe2', 'Washington');
insert into travels values('tr10', 'pe3', 'Kalkuta');
insert into travels values('tr11', 'pe3', 'Medina');
insert into travels values('tr12', 'pe4', 'Zagreb');
insert into travels values('tr13', 'pe4', 'Turin');
insert into travels values('tr14', 'pe1', 'Olimp');
insert into hotel_books values('hb1', 'pe1', 'tr1', 'Kimel');
insert into hotel_books values('hb2', 'pe2', 'tr2', 'Tynel');
insert into hotel_books values('hb3', 'pe3', 'tr3', 'Perel');
insert into hotel_books values('hb4', 'pe4', 'tr4', 'Turel');
insert into hotel_books values('hb5', 'pe5', 'tr5', 'Mawel');
insert into hotel_books values('hb6', 'pe5', 'tr6', 'Omel');
insert into hotel_books values('hb7', 'pe5', 'tr7', 'Gamel');
insert into hotel_books values('hb8', 'pe4', 'tr8', 'Remel');
insert into hotel_books values('hb9', 'pe4', 'tr9', 'Vemel');
insert into hotel_books values('hb10', 'pe5', 'tr1', 'Qarel');
insert into hotel_books values('hb11', 'pe4', 'tr2', 'Jawel');
insert into hotel_books values('hb12', 'pe4', 'tr3', 'Xamel');
insert into hotel_books values('hb13', 'pe3', 'tr4', 'Zemel');
insert into hotel_books values('hb14', 'pe5', 'tr4', 'Femel');
insert into hotel_books values('hb15', 'pe5', 'tr4', 'Remel');
insert into hotel_books values('hb16', 'pe4', 'tr4', 'Hemel');
insert into hotel_books values('hb17', 'pe4', 'tr4', 'Jemel');
insert into hotel_books values('hb18', 'pe5', 'tr4', 'Pemel');
insert into hotel_books values('hb19', 'pe5', 'tr4', 'Lemel');
How to make a query that will display all travels and the total number of hotel_books of a person. But the total number of hotel_books of a person should not take into account the records that he set for himself.
select
travels.tr_id,
count(hotel_books.pe_id <> travels.owner_id) as p_coun
from travels
LEFT OUTER JOIN peoples on travels.owner_id = peoples.pe_id
LEFT OUTER JOIN hotel_books on travels.tr_id = hotel_books.tr_id
group by travels.tr_id;
The result of my query does not display the real total number of hotel_books of a person:
now result expected result
| | tr_id| p_coun | | | tr_id| p_coun |
---------------------- ----------------------
| 1 | tr10 | 0 | | 1 | tr10 | 1 |
| 2 | tr7 | 1 | | 2 | tr7 | 2 |
| 3 | tr13 | 0 | | 3 | tr13 | 5 |
| 4 | tr8 | 1 | | 4 | tr8 | 3 |
| 5 | tr2 | 2 | | 5 | tr2 | 3 |
| 6 | tr14 | 0 | | 6 | tr14 | 2 |
| 7 | tr6 | 1 | | 7 | tr6 | 2 |
| 8 | tr4 | 8 | | 8 | tr4 | 5 |
| 9 | tr11 | 0 | | 9 | tr11 | 1 |
| 10 | tr3 | 2 | | 10 | tr3 | 1 |
| 11 | tr9 | 1 | | 11 | tr9 | 3 |
| 12 | tr5 | 1 | | 12 | tr5 | 0 |
| 13 | tr1 | 2 | | 13 | tr1 | 2 |
| 14 | tr12 | 0 | | 14 | tr12 | 5 |
---------------------- ----------------------
Work example: https://rextester.com/LTMNB15242