Join two tables

[bumbar]

Hallo!

I have two tables, MySQL

How do I can number off How many hotels and houses does each human have got

People

PersonID | Name

1 | BOB
2 | JOE
3 | ANDY

Oranges

OrangeID | PersonID | HOTELS | HOUSES

1 | 1 | someH1name | someV1name
2 | 1 | someH2name | someV2name
3 | 2 | someH1name | someV1name
4 | 3 | someH1name | someV1name

I tried to use that:

SELECT name, COUNT( * ) AS HOTELS 
FROM People
JOIN Oranges ON People.PersonID  = Oranges.OrangeID  

GROUP BY name

here is result:

Name | HOTELS |

BOB | 2

JOE | 1

ANDY | 1

But The result is sinful, how should it repairs the request?😕
I want that it to happen like this:

Name | HOTELS | HOUSES

BOB | 2 | 2
JOE | 1 | 1
ANDY | 1 | 1

:rolleyes:

[leatherback]

Hi,

Looks like you have a flawed database design..

What happens if a person has 2 houses and 3 hotels? You will end up with empty cells in your record.

You probably want a table like this for properties:

person_to_property
UserNr | PropertyNr

property
PropertyID | PName | Type

Then proceed as before joiining the three tables on userID = UserNr and PropertyNr = PropertyID, group by user.name, property.type