title: Counting Related Foreign Keys Goes Wrong
body: <p>I have the following tables : company, devices, items .</p>
  
  <p>items table content are linked to devices with foreign key, and devices are linked to company with another foreign key .</p>
  
  <p>What i want : </p>
  
  <p>Count the linked items to a selected record in devices table .</p>
  
  <p>The following query gets the devices list : </p>
  
  <pre><code>SELECT devices.device_id,devices.device_name
  FROM devices,company
  WHERE devices.for_id = (company.company_name='Sony') 
  ORDER BY devices.device_name ASC
  </code></pre>
  
  <p>Result : </p>
  
  <pre><code>id | device
  0  | Device 1 
  1  | Device 2
  </code></pre>
  
  <p>The following query gets the count correctly : </p>
  
  <pre><code>SELECT items.rom_name , COUNT(items.rom_name) AS 'count'
  FROM items
  WHERE items.device_for_id = 1
  </code></pre>
  
  <p>Result : </p>
  
  <pre><code>device | count
  Device 1 | 2
  Device 2 | 7
  </code></pre>
  
  <p>It's ok until now, i want to merge them together because i want to get it as a json response, So i've tried the following : </p>
  
  <pre><code>SELECT devices.device_name,devices.device_id,count(items.device_for_id) 
  AS 'roms_count'
  SFROM devices,company,items 
  SWHERE devices.for_id=(company.company_name='".$_GET['company_name']."') 
  SORDER BY devices.device_name ASC
  </code></pre>
  
  <p>Result : </p>
  
  <pre><code>Device | roms_count
  Device 1 | 15
  </code></pre>
  
  <p>Here's my problem, the roms count isn't correct and it's getting the sum of all records, Plus it's showing only one device .</p>
  
  <p><strong>device_for_id</strong> in items is a foreign key .</p>
  
  <p>What's the correct query for this ?</p>
  
  <p>Update : </p>
  
  <p>Company(company_id , company_name )</p>
  
  <p>devices(device_id , device_name , for_id(foreign) ) </p>
  
  <p>items(device_for_id (foreign) , rom_id , rom_name )</p>