Databases
StackOverflow2013
Postsdbo
POSQL Multiple COUNT() from two tables, within a LEFT JOIN
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Multiple COUNT() from two tables, within a LEFT JOIN
    primarykey
    data
    text
    <p>i have three tables, i want to select all data from one of the tables, and also collect a COUNT of how many times that specific row of data is linked in the other two tables.</p> <p>So, SELECT all data from site_projects. Then return a COUNT of site_project_members WHERE <code>site_projects</code>.<code>id</code> = <code>site_project_members</code>.<code>pid</code> and also return a COUNT of site_project_tasks WHERE <code>site_projects</code>.<code>id</code> = <code>site_project_members</code>.<code>pid</code></p> <p>I hope i'm making sense, to the query, it looks correct. and it querys the database (MySQL) with no problems. <strong>Except</strong> it returns a sum of both of the counts as both things. (see below the table structures)</p> <p>site_projects</p> <pre><code>id | title | desc | start | deadline | progress 1 | Project 1 | a project | 1321748906 | 1329847200 | 20 </code></pre> <p>site_project_members</p> <pre><code>id | pid | uid | img | hidden 1 | 1 | 1 | 1 | 0 2 | 1 | 2 | 2 | 0 </code></pre> <p>site_project_tasks</p> <pre><code>id | pid | desc | completed 1 | 1 | Task 1 | 1 1 | 1 | Task 2 | 0 </code></pre> <p>Here is my query:</p> <pre><code>SELECT p.`id`, p.`title`, p.`desc`, p.`progress`, p.`start`, p.`deadline`, COUNT(m.`id`) as `members`, COUNT(t.`id`) as `tasks` FROM `site_projects` p LEFT JOIN `site_project_members` m ON p.`id`=m.`pid` LEFT OUTER JOIN `site_project_tasks` t ON p.`id`=t.`pid` ORDER BY p.`id` ASC </code></pre> <p>The result i get is:</p> <pre><code>id | title | desc | progress | start | deadline | members | tasks 1 | Project 1 | a project | 20 | 1321748906 | 1329847200 | 4 | 4 </code></pre> <p>Both of the "4" values should be 2. however they are not :S Anyone able to help? Much Appreciated</p> <p>Thanks, Dan</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdgw
    1. USDan Spiteri
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Multiple COUNT() from two tables, within a LEFT JOIN
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Multiple COUNT() from two tables, within a LEFT JOIN
      1. USvenividivicki
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Multiple COUNT() from two tables, within a LEFT JOIN
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is because you are aggregating wrong! MySQL, unlike other RDBMS, won't stop you from running aggregations like this. Basically you get random values back since you don't specify a `GROUP BY`.
      singulars
      1. POSQL Multiple COUNT() from two tables, within a LEFT JOIN
      1. USJNK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload