[SOLVED] Query help between multiple tables

[Scorpy]

I have three tables, one for the user, one for the town and one for the buildings. My problem is that I am attempting to use just one statement to fetch as SUM of information from the buildings table for all the towns that a user owns, fetching the information for a single town is easy as the building table contains a column for the town_id that it belongs to, however it does not contain a column for the user as I believed it would be a waste.

 

So to put that into a scheme that might be easier to follow than a tired soul at 4am in the morning.. (Stripped down for even easier reading)

 

CREATE TABLE `users` (
   `user_id`                     int unsigned        NOT NULL,

   PRIMARY KEY (user_id)
) ENGINE=MyISAM;

CREATE TABLE `towns` (
   `town_id`                int unsigned         NOT NULL auto_increment,
   `town_owner_id`          int unsigned         NOT NULL default '0',

   PRIMARY KEY (town_id),
   KEY (town_owner_id)
) ENGINE=MyISAM;

CREATE TABLE `buildings` (
   `building_workers`           smallint unsigned  NOT NULL default '0',
   `building_town_id`           int unsigned       NOT NULL,

   PRIMARY KEY town_slot (building_slot,building_town_id)
) ENGINE=MyISAM;

 

I'm needing to try and get the SUM of all building_workers from EVERY user owned town. I have been stumped on this for the majority of the day and all of my attempts have been unsuccessful. I am still very new to MySQL so please be gentle if it is an easy problem for you guys, I'm just failing to see it or if it can be done with just the variables above.

 

Any help with this would be very much appreciated!

[Mchl]

SELECT SUM(b.building_workers) AS sum_workers
FROM buildings AS b
INNER JOIN towns AS t ON (b.building_town_id = t.town_id)
INNER JOIN users AS u ON (t.town_owner_id = u.user_id)
WHERE
u.user_id = ?

[Scorpy]

Thank you very much!!