SQL组内连接无法获得所有结果

Question

I really trying to get this SQL to work. Im no expert so really cant figure this out.

            $sqlquery = " SELECT 
    s.searchword AS searchword, 
    s.id AS id, 
    COUNT( c.id ) AS searchresult, 
    s.region AS region 
    FROM search_words AS s 
    INNER JOIN company_data AS c ON 
    c.branch_text LIKE CONCAT(  '%', s.searchword,  '%' )       
    GROUP BY 1 ORDER BY s.date DESC";

This gives me:

      Array
    (
[0] => Array
    (
        [searchword] => WHOLESALE
        [searchid] => 427
        [searchresult] => 98
        [region] => stockholm
    )

[1] => Array
    (
        [searchword] => cars
        [searchid] => 426
        [searchresult] => 26
        [region] => 
    )

[2] => Array
    (
        [searchword] => Retail
        [searchid] => 342
        [searchresult] => 41
        [region] => stockholm
    )

[3] => Array
    (
        [searchword] => Springs
        [searchid] => 339
        [searchresult] => 4
        [region] => stockholm
    )

[4] => Array
    (
        [searchword] => Leasing
        [searchid] => 343
        [searchresult] => 2
        [region] => stockholm
    )

[5] => Array
    (
        [searchword] => Food
        [searchid] => 340
        [searchresult] => 37
        [region] => stockholm
    )

 )

But, it does not give me any of the other results where there are no searchhits, would retur something like [searchresult] => 0. Meaning they do not group as I wish, because there are no such searchwords within the company_data table.

How can I fix this, please help :(

EDIT:

Here is the full code:

      public function getUserSearches()
    {

    $sqlquery = " SELECT 
    s.searchword AS searchword, 
    s.id AS id, 
    s.userId AS userid, 
    COUNT( c.id ) AS searchresult, 
    s.region AS region 
    FROM search_words AS s 
    INNER JOIN company_data AS c ON 
    c.branch_text LIKE CONCAT(  '%', s.searchword,  '%' )       
    GROUP BY 1 ORDER BY s.date DESC";

     // IS THERE ANYTHING WRONG HERE?? LIKE IT DOES NOT MATCH AGAINST THE USER?
    $result = $this->dbh->query($sqlquery, array(":userId" => $this->user_id));

    $arr = array();
    foreach ($result as $item)  {
        array_push($arr, array('searchword' => $item['searchword'], 'searchid' => $item['id'], 
    'searchresult' => $item['searchresult'], 'userid' => $item['userid'],
    'region' => $item['region']));
    }

        return json_encode($arr);
    return print_r($arr);
    }

Answer 1

Use LEFT JOIN so that any row that doesn't match the search criteria in the join condition will come with null, therefore count will be 0. Something like this:

SELECT 
  s.searchword AS searchword, 
  s.id AS id, 
  s.region AS region,
  COUNT(COALESCE(c.id, 0)) AS searchresult
FROM search_words AS s 
LEFT JOIN company_data AS c 
        ON c.branch_text LIKE CONCAT(  '%', s.searchword,  '%' )       
GROUP BY s.searchword, s.id, s.region
ORDER BY s.date DESC;

See this for more details about the SQL Join types.