JOIN three tables

Hallo!

I have three 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


HOTELS

hotelID | PersonID | HOTELS
---------------------------
1 | 1 | somehotel
2 | 1 | somehotel
3 | 2 | somehotel
4 | 3 | somehotel
5 | 3 | somehotel
6 | 3 | somehotel


HOUSES

houseID | PersonID | HOUSES
---------------------------
1 | 1 | somehouse
2 | 1 | somehouse
3 | 2 | somehouse
4 | 2 | somehouse
5 | 2 | somehouse
6 | 3 | somehouse


I want to know how each human have got hotels and houses

If I write this (JOIN two tables)

SELECT name, count(*) AS hotels FROM People JOIN hotels ON 
People.PersonID=HOTELS.hotelID  GROUP BY name

PHP:

name| hotels
------------------------
BOB | 2
JOE | 1
ANDY | 3

I receive good solution

but if I write this (JOIN three tables)

SELECT name, count(*) AS hotels,  count(*) AS houses 
FROM People  
JOIN hotels ON  People.PersonID=HOTELS.hotelID 
JOIN houses ON People.PersonID=HOUSES.houseID 
GROUP BY name

PHP:

name| hotels | houses
------------------------
BOB | 2 | 2
JOE | 1 | 1
ANDY | 3 | 3

The result is inappropriate


Can somebody help?

 

You will need to do two separate queries to run this unless you can tell mysql to reference the user that you're looping on.

Also your JOIN statements don't look right.

I think this should be: People.PersonID=HOTELS.PersonID, same thing for People.PersonID=HOUSES.PersonID

If you can loop, this should work:

SELECT name, count(hotelID) AS hotels,  
(SELECT count(houseID ) FROM People INNER JOIN houses ON People.PersonID=HOUSES.PersonID WHERE name = 'SOME_NAME') AS houses
FROM People  INNER JOIN hotels ON  People.PersonID=HOTELS.PersonID
 WHERE name = 'SOME_NAME';

PHP:

 

Thankx.

I tested you method - no result ...

 

You would need to query for all the names or PersonID's first, then loop through using the second query for the counts.

$query =  mysql_query("SELECT PersonID FROM People");

while($result = mysql_fetch_array($query)):

$query2 = mysql_query( "
SELECT name, count(hotelID) AS hotels,  
(SELECT count(houseID ) FROM People INNER JOIN houses ON People.PersonID=HOUSES.PersonID WHERE PersonID = '".$result['PersonID']."') AS houses
FROM People  INNER JOIN hotels ON  People.PersonID=HOTELS.PersonID
 WHERE PersonID = '".$result['PersonID']."'
");


endwhile;

PHP: