0

Here is what I need to achieve:

person_id   last_name   first_name  region_id   region name                         
  1         barnum        phineas      1        maricopa    
  2         loman         willy        2        pima        
  2         loman         willy        3        pinal       
  2         loman         willy        4        santa cruz      
  3         kay           mary         5        cochise     
  3         kay           mary         6        gila       
  3         kay           mary         7        graham      
  4         lillian       vernon       NULL     NULL

Here are my tables:

Create database sales
use sales

create table if not exists
`Sales_People` 
(`person_id` int primary key,
`last_name` char(16) not null,
`first_name` char(16) not null);
INSERT INTO Sales_people (`person_id`, `last_name`, `first_name`)
values 
('1', 'barnum', 'phineas'),
('2', 'loman', 'willy'),
('3', 'kay', 'mary'),
('4', 'lillian', 'vernon');
create table if not exists
`Sales_Region` 
(`region_id` int primary key,
`name` char(16) not null);
INSERT INTO Sales_Region (`region_id`, `name`)
Values
('1', 'maricopa'),
('2', 'pima'),
('3', 'pinal'),
('4', 'santa cruz'),
('5', 'cochise'),
('6', 'gila'),
('7', 'graham');

create table if not exists
`Sales_People_Region` 
(`person_id` int not null,
`region_id` int not null,
constraint spr_pk primary key(person_id, region_id),
constraint spr_fk1 foreign key(person_id) 
references Sales_People(person_id),
constraint spr_fk2 foreign key(region_id) 
references Sales_Region(region_id));

INSERT INTO Sales_People_Region (`person_id`, `region_id`)
Values
('1', '1'),
('2', '2'),
('2','3'),
('2', '4'),
('3', '5'),
('3', '6'),
('3','7');

create table if not exists
`Sales` (`year` int not null,
`month` int not null,
`region_id` int not null,
`amount_sold` decimal(11,2),
constraint s_pk primary key(year, month, region_id),
constraint s_fk foreign key(region_id) references 
Sales_Region(region_id));

INSERT INTO Sales (`year`, `month`, `region_id`, `amount_sold`)
Values
('2016', '01', '1', '800000'),
('2016', '02', '1', '850000'),
('2016', '03', '1', '990000'),
('2016', '01', '2', '425000'),
('2016', '02', '2', '440000'),
('2016', '03', '2', '450000'),
('2016', '01', '3', '200000'),
('2016', '02', '3', '210000'),
('2016', '03', '3', '220000'),
('2016', '01', '4', '50000'),
('2016','02', '4', '52000'),
('2016', '03', '4', '55000'),
('2016', '01', '5', '40000'),
('2016', '02', '5', '41000'),
('2016', '03', '5', '42000'),
('2016', '01', '6', '3000'),
('2016', '02', '6', '31000'),
('2016','03', '6', '32000'),
('2016', '01', '7', '20000'),
('2016', '02', '7', '21000'),
('2016', '03', '7', '22000');

Here is my query:

select sales_people.person_id, `last_name`, `first_name`, sales_region.Region_id, 
trim(sales_region.`name`) AS 'Region Name'   
 from `sales_region` 
 inner join sales_people_region on sales_people_region.region_id = sales_region.region_id 
  inner join sales_people on sales_people_region.`person_id` = sales_people.`person_id`
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

What I get:

person_id   last_name   first_name  Region_id   "Region Name"
1       barnum      phineas 1       maricopa
2       loman       willy       2       pima
2       loman       willy       3       pinal
2       loman       willy       4       "santa cruz"
3       kay     mary                5       cochise
3       kay     mary                6       gila
3       kay     mary               7        graham

I can't get it to let me see the last person_id because they have no region_ids assigned to them. In the table sales_people_region region_id is a primary key and therefore not able to be null. If I query sales_people and group by person_id she does come up.

3
  • Stackoverflow...
    – amanuel2
    Apr 24, 2016 at 19:35
  • You need to use left outer join instead of inner.
    – mustaccio
    Apr 24, 2016 at 21:26
  • you are quite correct Dsafds. its what i ended up doing after some help.
    – talesin22
    Apr 24, 2016 at 21:29

1 Answer 1

0

did you try outer join?

select sales_people.person_id, last_name, first_name, sales_region.Region_id, coalesce(trim(sales_region.name),'NULL')  AS 'Region Name' 
from sales_region 
right outer join sales_people_region on sales_people_region.region_id = sales_region.region_id
right outer join sales_people on sales_people_region.`person_id` = sales_people.person_id
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

`

1
  • i used left outer join instead of right after some advice and it worked exactly as it should.
    – talesin22
    Apr 24, 2016 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.