I read a lot of topics on this subject, but I can't figure it out for my project. I have two tables, grids and points. It is possible that there are multiple points for 1 grid, however, I only want one result of the points table (doesn't matter which of the multiple points) or NULL when there are no entries in points for that grid.
With the query:
SELECT g.id, g.latStart, g.latEnd, g.lngStart, g.lngEnd, p.teamId
FROM grids g LEFT JOIN points p ON p.gridId = g.id
I will receive a list of all the grid entries, plus points. The result, however, is not how I would want it.
I cannot place images yet. Here is the result in JSON as I cannot place images:
{
"data":
[
{
"id": 1,
"latStart": "51.878347",
"latEnd": "4.388849",
"lngStart": "51.884847",
"lngEnd": "4.399849",
"teamId": 2
},
{
"id": 1,
"latStart": "51.878347",
"latEnd": "4.388849",
"lngStart": "51.884847",
"lngEnd": "4.399849",
"teamId": 2
},
{
"id": 1,
"latStart": "51.878347",
"latEnd": "4.388849",
"lngStart": "51.884847",
"lngEnd": "4.399849",
"teamId": 2
},
{
"id": 2,
"latStart": "51.878347",
"latEnd": "4.399849",
"lngStart": "51.884847",
"lngEnd": "4.410849",
"teamId": null
},
{
"id": 3,
"latStart": "51.878347",
"latEnd": "4.410849",
"lngStart": "51.884847",
"lngEnd": "4.421849",
"teamId": null
},
{
"id": 4,
"latStart": "51.878347",
"latEnd": "4.421849",
"lngStart": "51.884847",
"lngEnd": "4.432849",
"teamId": null
}
]
}
I receive three times the grid with id 1, as there are three points. How can I limit the received points to one? I've tried sub querys, but I can't get them to work.
Thanks in advance for helping me out!
I only want one result of the points table (doesn't matter which of the multiple points) or NULL when there are no entries in points for that grid.
if that is the case then you can group by and take either the Minimum or Maximum point.teamId
SELECT
g.id
, g.latStart
, g.latEnd
, g.lngStart
, g.lngEnd
, MAX(p.teamId) teamId
FROM grids g
LEFT JOIN points p ON p.gridId = g.id
GROUP BY
g.id
, g.latStart
, g.latEnd
, g.lngStart