talesin22 talesin22 - 7 months ago 17
SQL Question

mysql grouping, joins and order by problems

I need the result to look like this:

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


my code is this:

select `person_id`, `last_name`, `first_name`,
`Region_id`, `name` AS 'Region Name'
from `sales_region`
inner join sales_people
on `person_id` = `person_id`
group by `region_id` asc, `person_id`
having `person_id`in ('1','2','3')
order by `person_id`,`region_id` asc
;


it gives me this:

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


I am not sure how to make it so it shows like it does above. I have tried messing with the order by and group by and i get the same kind of results. I am not sure how to narrow it down to how the results should be.

Answer

Try with:

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`

where sales_people.person_id in ('1','2','3')
group by  sales_region.region_id, sales_people.person_id
order by sales_people.person_id, sales_region.region_id asc;
Comments