sqlite - 连接不同行中的两个值

I have 2 tables that look like this:

我有2个表看起来像这样：

CREATE TABLE places(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  type TEXT
  ...  
)

CREATE TABLE meta(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  place INTEGER NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  FOREIGN KEY(place) REFERENCES places(id) ON DELETE CASCADE
)

The meta table is a "eav" like table.

元表是一个像表一样的“eav”。

Now I want to select all records that have "lat" and "lng" attributes and join lat" and "lng" with a space in the result

现在我想选择所有具有“lat”和“lng”属性的记录，并在结果中用空格连接lat“和”lng“

This is my current query

这是我目前的查询

SELECT p.id, m1.value || " " || m2.value FROM places p 
   JOIN meta m1 ON m1.place = p.id 
   JOIN meta m2 ON m2.place = p.id 
      WHERE p.type IN ("1")
       AND m1.key = "lat"
       AND m2.key = "lng";

It appears it works. But is this really correct? Can I do it using a single join?

它似乎有效。但这真的是对的吗？我可以使用一次加入吗？

1 个解决方案

#1

Single quotes, specify your JOIN types and your condition is only looking for a single result from p.type so use = rather than IN.

单引号，指定您的JOIN类型，您的条件只是从p.type中查找单个结果，因此使用=而不是IN。

SELECT p.id, m1.value || ' ' || m2.value 
FROM places p 
INNER JOIN meta m1 ON m1.place = p.id 
INNER JOIN meta m2 ON m2.place = p.id 
WHERE p.type = '1'
AND m1.key = 'lat'
AND m2.key = 'lng';