使用LEFT JOIN和LIKE条件的MySQL查询没有显示我的预期

问题描述：

I'm trying to make a query that return data from 4 tables, the problem comes when I try to put some aditional conditions. The query works with 4 tables with LEFT JOIN without the conditions but when I put the LIKE condition nothing change in the result. The result must be the select with "lat" that only begins with 40.95

This code it's working:

if($resultset=getSQLResultSet("SELECT name, lat, lng, description, 
COALESCE(section, 0), COALESCE(AVG(score), 0) FROM places P LEFT JOIN ratings R ON P.id=R.place LEFT JOIN ratings_sections RS ON R.id=RS.rating LEFT JOIN categories C ON P.type=C.type GROUP BY P.name, RS.section ORDER BY P.name, RS.section")){
while ($row = $resultset->fetch_array(MYSQLI_NUM)){
    echo json_encode($row);
    }
}

But nothing change when I put the LIKE condition:

if($resultset=getSQLResultSet("SELECT name,lat, lng, description, COALESCE(section, 0), COALESCE(AVG(score), 0) FROM places P LEFT JOIN ratings R ON P.id=R.place AND P.lat LIKE '40.95%' LEFT JOIN ratings_sections RS ON R.id=RS.rating LEFT JOIN categories C ON P.type=C.type GROUP BY P.name, RS.section ORDER BY P.name, RS.section")){
    while ($row = $resultset->fetch_array(MYSQLI_NUM)){
        echo json_encode($row);
    }
}

Result of the query

我正在尝试从4个表中返回数据的查询，当我尝试放置一些表时出现问题附加条件。该查询使用4个表，LEFT JOIN没有条件，但是当我放置LIKE条件时，结果中没有任何变化。结果必须是选择“lat”只以40.95开头 p>

这段代码正常工作： p>

  if（$ resultset =  getSQLResultSet（“SELECT name，lat，lng，description，
 nCOALESCE（section，0），COALESCE（AVG（score），0）FROM places P LEFT JOIN rating R ON P.id = R.place LEFT JOIN ratings_sections RS ON R  .id = RS.rating LEFT JOIN类别C ON P.type = C.type GROUP BY P.name，RS.section ORDER BY P.name，RS.section“））{
while（$ row = $ resultset-＆gt  ; fetch_array（MYSQLI_NUM））{
 echo json_encode（$ row）; 
} 
} 
  code>  pre> 
 
 但是当我放置LIKE条件时没有任何改变： 
 
 
  if（$ resultset = getSQLResultSet（“SELECT name，lat，lng，description，COALESCE（section，0），COALESCE（AVG（score），0）FROM places P LEFT  JOIN等级R ON P.id = R.place AND P.lat LIKE '40 .95％'LEFT JOIN ratings_sections RS ON R.id = RS.rating LEFT JOIN category C ON P.type = C.type GROUP BY P.name，  RS.section ORDER BY P.name，RS .section“））{
 while（$ row = $ resultset-＆gt; fetch_array（MY  SQLI_NUM））{
 echo json_encode（$ row）; 
} 
} 
  code>  pre> 
 
  查询结果  p> 
  div>

答

The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements.

SELECT name,lat, lng, description,... FROM

and

SELECT score FROM

Are different

答

Your third query is wrong, because you're making a UNION of two queries, where the first one consists of four columns, and the second one has just one column.

SELECT name, lat, lng, description
FROM places P, categories C
WHERE P.lat LIKE '40.96%'
OR lat LIKE '40.95%'
AND P.type=C.type
GROUP BY P.name
UNION
SELECT score
FROM ratings_sections RS, rating R
WHERE RS.rating=R.id

Your fourth query is also wrong, for example, here:

LEFT JOIN GROUP BY P.name, RS.section ORDER BY P.name, RS.section

You can't use a GROUP BY clause inside a JOIN for obvious reasons.

使用LEFT JOIN和LIKE条件的MySQL查询没有显示我的预期

相关推荐