AndreaNobili AndreaNobili - 27 days ago 6
SQL Question

How can I implement a query that return a single record containing the informations contained in 2 specific record?

I am not so into SQL and I have the following problem.

So basically I have a table containing the following fields:

id: BigInt
geographical_position: Geometry
// SOME OTHER FIELDS IRRILEVANT FOR MY QUERY


I want select a single record containg the geographical_position values of 2 specific record (have to be presented on the same record), so I try to do something like this:

SELECT
a.geographical_position AS point1,
a.geographical_position AS point2
FROM accomodation a

where a.id = 31 and a.id = 32


But it seems to be wrong because obtain an empty result set.

Basically I want to obtain the geographical_position of the record having id=31 in a cell named point1 and the geographical_position of the record having id=32 in a cell named point2.

How can I do? What is wrong in my query? How can I fix it?

vkp vkp
Answer

I think you can do it with a self join.

SELECT
  a1.geographical_position AS point1,
  a2.geographical_position AS point2
FROM accomodation a1
JOIN accomodation a2 ON a1.id=31 and a2.id=32

The query you have currently is wrong as the id can not have 2 values at the same time.

Comments