[SOLVED] sql query involving counting rows in many to many relationships

[machupicchu]

Hey everyone.  I'm new to these forums, but so far they seem very informative.  Thank you for all who contributed! 

 

I would like to ask a question on an sql query.

 

For this question, we'll be using buildings and tenants as example data:

 

 

Using these tables, I would answer this simple question:

List all building ids with their number of floors and apartments

 

With a simple sql like this:

SELECT `idbuilding`, `floors`, `appartments` FROM buildings

 

That would return a number of rows containing the ids, floors and apartments.

 

Now my question is this:  is it possible, in the same query, to also return the number of tenants that the building contains?

 

I feel this can be done, using JOINS, COUNT or maybe using inner queries, but couldn't figure it out.

 

Thanks for your help 

 

and don't worry if the tables aren't properly normalized, these are only for the example.

Also this convenient spellchecker feature pointed out that i spelled apartments wrong

 

[attachment deleted by admin]

[xtopolis]

SELECT b.idbuilding, b.floors, b.apartments, COUNT(bht.tenants_idtenant) FROM buildings b 
LEFT JOIN buildings_house_tenants bht
ON (bht.buildings_idbuilding = b.idbuilding)
GROUP BY b.idbuilding
ORDER BY b.idbuilding ASC

Results:

[pre]idbuilding    floors    apartments    COUNT( bht . tenants_idtenant )

1              2          20            15

2              3          30            8

3              1          5              0

4              5          100            0[/pre]

Using data:

-- Table structure for table `buildings`
-- 

CREATE TABLE `buildings` (
  `idbuilding` int(11) NOT NULL auto_increment,
  `floors` smallint(2) NOT NULL,
  `apartments` smallint(2) NOT NULL,
  `address` varchar(255) collate latin1_german2_ci NOT NULL,
  PRIMARY KEY  (`idbuilding`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `buildings`
-- 

INSERT INTO `buildings` VALUES (1, 2, 20, '123 Fake St');
INSERT INTO `buildings` VALUES (2, 3, 30, '456 Fake St');
INSERT INTO `buildings` VALUES (3, 1, 5, '555 Fake St');
INSERT INTO `buildings` VALUES (4, 5, 100, '100 Fake St');

-- Table structure for table `buildings_house_tenants`
-- 

CREATE TABLE `buildings_house_tenants` (
  `buildings_idbuilding` int(11) NOT NULL,
  `tenants_idtenant` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `buildings_house_tenants`
-- 

INSERT INTO `buildings_house_tenants` VALUES (1, 1);
INSERT INTO `buildings_house_tenants` VALUES (1, 3);
INSERT INTO `buildings_house_tenants` VALUES (1, 5);
INSERT INTO `buildings_house_tenants` VALUES (1, 7);
INSERT INTO `buildings_house_tenants` VALUES (1, 9);
INSERT INTO `buildings_house_tenants` VALUES (1, 11);
INSERT INTO `buildings_house_tenants` VALUES (1, 13);
INSERT INTO `buildings_house_tenants` VALUES (1, 15);
INSERT INTO `buildings_house_tenants` VALUES (1, 17);
INSERT INTO `buildings_house_tenants` VALUES (1, 19);
INSERT INTO `buildings_house_tenants` VALUES (1, 20);
INSERT INTO `buildings_house_tenants` VALUES (1, 21);
INSERT INTO `buildings_house_tenants` VALUES (1, 22);
INSERT INTO `buildings_house_tenants` VALUES (1, 23);
INSERT INTO `buildings_house_tenants` VALUES (2, 2);
INSERT INTO `buildings_house_tenants` VALUES (2, 4);
INSERT INTO `buildings_house_tenants` VALUES (2, 6);
INSERT INTO `buildings_house_tenants` VALUES (2, ;
INSERT INTO `buildings_house_tenants` VALUES (2, 10);
INSERT INTO `buildings_house_tenants` VALUES (2, 12);
INSERT INTO `buildings_house_tenants` VALUES (2, 14);
INSERT INTO `buildings_house_tenants` VALUES (2, 16);
INSERT INTO `buildings_house_tenants` VALUES (1, 18);

-- Table structure for table `tenants`
-- 

CREATE TABLE `tenants` (
  `idtenant` int(11) NOT NULL auto_increment,
  `name` varchar(45) collate latin1_german2_ci NOT NULL,
  `rent` smallint(4) NOT NULL,
  PRIMARY KEY  (`idtenant`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=24 ;

-- 
-- Dumping data for table `tenants`
-- 

INSERT INTO `tenants` VALUES (1, 'Chris', 2134);
INSERT INTO `tenants` VALUES (2, 'Bill', 3255);
INSERT INTO `tenants` VALUES (3, 'Fhris', 2134);
INSERT INTO `tenants` VALUES (4, 'Ehris', 2234);
INSERT INTO `tenants` VALUES (5, 'Shris', 2134);
INSERT INTO `tenants` VALUES (6, 'Chrdsss', 2134);
INSERT INTO `tenants` VALUES (7, 'Chrd', 2134);
INSERT INTO `tenants` VALUES (8, 'dfshris', 3134);
INSERT INTO `tenants` VALUES (9, 'CSSris', 2134);
INSERT INTO `tenants` VALUES (10, 'ChDis', 2134);
INSERT INTO `tenants` VALUES (11, 'Chrif', 1134);
INSERT INTO `tenants` VALUES (12, 'Chrsd', 2134);
INSERT INTO `tenants` VALUES (13, 'Chris', 2134);
INSERT INTO `tenants` VALUES (14, 'Chdfis', 2134);
INSERT INTO `tenants` VALUES (15, 'Ccxs', 2133);
INSERT INTO `tenants` VALUES (16, 'C4is', 2134);
INSERT INTO `tenants` VALUES (17, 'CaSris', 2134);
INSERT INTO `tenants` VALUES (18, 'Chevis', 7134);
INSERT INTO `tenants` VALUES (19, 'Cais', 9134);
INSERT INTO `tenants` VALUES (20, 'Ch5is', 2134);
INSERT INTO `tenants` VALUES (21, 'ais', 8134);
INSERT INTO `tenants` VALUES (22, 'CFris', 8134);
INSERT INTO `tenants` VALUES (23, 'ahris', 2133);

[machupicchu]

Wow    Thanks xtopolis for such a quick an thorough response.

 

I will analyze it until I understand every bit.

 

1 question.  How long did it take you to reach this level of sql proficiency and how did you do it?

 

[xtopolis]

Hi,

I wouldn't say that I'm that proficient in SQL.  I know bits and pieces, and I know possible ways to reach a solution... but not necessarily the best ways.  I didn't know about (how to use) joins until a few months ago after reading Barand's tutorial.  Also, having a basic understanding of normalization has helped me a lot.

 

I have been learning PHP/MYSQL for years [5+], but only started getting serious since I registered on this site.  You will really start to learn something when you need to use it and you need it to work well.  I "knew" how to program in php/mysql... but what I was doing was very error prone, tedious, as well as 'old school'.  At this point I'm just trying to get my head around OOP, Database normalization, etc, etc.. and learning things as I go along.

 

Long story short, I can find out an answer most of the time, but it's people like fenway that really know their stuff that amazes me with their simple answers or quick understandings from peoples' complicated explanations of their database problems.

[machupicchu]

Barand's tutorial is great, very clear and informative.  I learned more from it on practical sql queries than from a 500 page book