CODEWITHSUNDEEP
phpmysqlsqlleft-joinsql-like
Raquel
Raquel

Reputation: 23

MySQL query with LEFT JOIN and LIKE conditions doesn't show what I expected

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

Upvotes: 1

Views: 91

Answers (2)

Ildar Akhmetov
Ildar Akhmetov

Reputation: 1431

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.

Upvotes: 2

Indent
Indent

Reputation: 4967

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

Upvotes: 1

Related Questions