0 
SELECT place.PlaceID, place.Location, place.PlaceName, place_img.ImgPath FROM place LEFT JOIN place_img ON place.PlaceID = place_img.PLACE_PlaceID GROUP BY place_img.PLACE_PlaceID

The above SQL query works fine if I used it without the GROUP BY clause. I don't understand what the problem is with the GROUP BY clause.

If I run the query without using GROUP BY clause:

enter image description here

But if I run the query with the GROUP BY clause, then I get:

enter image description here

Why is this happening?

Improve this question
2
  • The bigger question is why do you think you need GROUP BY here in the first place. GROUP BY is mainly useful if you want to take aggregates (e.g. sums, averages) over groups of records. If you are already happy with the first version of your query, then there is no need to spruce it up by adding GROUP BY. Commented Apr 30, 2018 at 15:05
  • actually there are 2 tables named as place and place_img. there are more than 1 record in place_img table for one PlaceID (PlaceID is the primary key on place table and it's the foreign key of the place_img table). I need to select only one image from the place_img table for each record in the place table. Commented Apr 30, 2018 at 15:15

2 Answers 2

Reset to default
1

If you really just one image path per place, and you don't mind which path you get, then you may try this:

SELECT p.PlaceID, p.Location, p.PlaceName, MAX(pi.ImgPath) AS ImgPath FROM place p LEFT JOIN place_img pi ON p.PlaceID = pi.PLACE_PlaceID GROUP BY p.PlaceID, p.Location, p.PlaceName;

Note that if place.PlaceID be the primary key of that table, then you may simplify the GROUP BY clause to the following:

GROUP BY p.PlaceID
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Also I have another question please. This same database and same table with same data are in another machine. If I run the SQL Quary in there it work fine and return the same data set as I needed. May I know why?
@A.Madhushani A speculation, but one possibility is that the ONLY_FULL_GROUP_BY modes are different between the two MySQL servers. Depending on the mode, your current GROUP BY query might return the same as my answer, or it might not even run at all. The bottom line is that you should write proper code; my answer is one possibility.
0

You can not use GROUP BY like this, because you did not specify what to do with the aggregated data.

Your group by should be in form of:

SELECT AGGRATION_FUNCTION(FIELD_1), FIELD_2 FROM TABLE GROUP BY Country;

This way, the SGDB know what to do with field_1 when he has to agregate data

Example of how to use GROUP BY : https://www.w3schools.com/sql/sql_groupby.asp

If you are not sure what to do with the aggregated data, it might be because you are not really wanting to use a GROUP BY or you are not selecting the right field.

Improve this answer

Comments