Not sure how to make the correct mySQL query to get data from multiple tables in 1 query <p>I need to get data from multiple tables from a database and I need to use 1 query, but I can't get it to work.</p> <p>I got these table:<br> <b>projects:</b></p> <pre><code>id name start_date end_date project_leader finished 1 project_1 2012-08-01 00:00:00 2012-29-01 00:00:00 2 0 </code></pre> <p><b>users</b></p> <pre><code>id username password email status 1 user_1 pass_1 email_1 1 2 user_2 pass_2 email_2 1 </code></pre> <p><b>user_has_project</b></p> <pre><code>userid projectId 1 1 </code></pre> <p><b>tasks</b></p> <pre><code>id project description end_date user 1 1 test description 1 2012-29-01 00:00:00 1 2 1 test description 2 2012-29-01 00:00:00 1 </code></pre> <p>So what I need to do, is make a query that should give me this result:<br> <b>Result:</b></p> <pre><code>project_id project_name start_date end_date project_leader finished tasks 1 project_1 2012-08-01 00:00:00 2012-29-01 00:00:00 user_2 0 2 </code></pre> <p>I got it to work until the part where I need to count the amount of tasks that a project has. I got this query, but that doesn't work:</p> <pre><code>SELECT projects.id, projects.name, projects.start_date, projects.end_date, projects.finished, users.username AS project_leader, COUNT(tasks.id) AS tasks FROM projects, tasks INNER JOIN user_has_project ON user_has_project.projectId = projects.id INNER JOIN users ON projects.project_leader = users.id WHERE user_has_project.userId = 1 </code></pre> <p>SQL dump, so people can try to test their query for me: </p> <pre><code>-- phpMyAdmin SQL Dump -- version 3.4.5 -- http://www.phpmyadmin.net -- -- Machine: localhost -- Genereertijd: 20 aug 2012 om 19:42 -- Serverversie: 5.5.16 -- PHP-Versie: 5.3.8 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `project-deadline` -- -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `projects` -- CREATE TABLE IF NOT EXISTS `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `start_date` datetime DEFAULT NULL, `end_date` datetime DEFAULT NULL, `project_leader` int(11) DEFAULT NULL, `finished` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Gegevens worden uitgevoerd voor tabel `projects` -- INSERT INTO `projects` (`id`, `name`, `start_date`, `end_date`, `project_leader`, `finished`) VALUES (1, 'Project 1', '2012-08-01 00:00:00', '2012-09-18 00:00:00', 1, 0); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `tasks` -- CREATE TABLE IF NOT EXISTS `tasks` ( `id` int(11) NOT NULL AUTO_INCREMENT, `project` int(11) DEFAULT NULL, `description` varchar(100) DEFAULT NULL, `end_date` datetime DEFAULT NULL, `user` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `password` varchar(32) NOT NULL, `email` varchar(100) NOT NULL, `status` int(11) NOT NULL, `timezone` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Gegevens worden uitgevoerd voor tabel `users` -- INSERT INTO `users` (`id`, `username`, `password`, `email`, `status`, `timezone`) VALUES (1, 'DijkeMark', '37540da17c71d40c656b97b32c00f692', 'mark.dijkema@gmail.com', 1, 'UP1'); -- -------------------------------------------------------- -- -- Tabelstructuur voor tabel `user_has_project` -- CREATE TABLE IF NOT EXISTS `user_has_project` ( `userId` int(11) NOT NULL, `projectId` int(11) NOT NULL, PRIMARY KEY (`userId`,`projectId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Gegevens worden uitgevoerd voor tabel `user_has_project` -- INSERT INTO `user_has_project` (`userId`, `projectId`) VALUES (1, 1), (1, 6); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; </code></pre>