sqlite - LEFT OUTER JOIN 查询过滤器id

Question

我正在开发一个 android 应用程序，如果他们有这个项目，我正在使用 CursorAdapter 显示一个带有名称和复选框的 TextView。我的问题是管理光标，因为您可以看到有时名称是重复的。

我有两张 table :

表A:

_id | Name | Attend             1 --- John ---- 1           2 --- Peter --- 1               3 --- Jean ---- 0               4 --- Finn ---- 1               5 --- Jake ---- 1

TABLE B:

_id | _idA | Item12 --- 1 ---- Yogurt21 --- 2 ---- Yogurt33 --- 1 ---- Wine25 --- 2 ---- KitKat

I am looking for a JOIN Table with these conditions:Attend = 1 , Item = Yogurt and all the names in Table A without repeating, this is the result I am looking for:

Id |    Name    |   Attend  |   _id |   _idA | Item1 -- John ----- 1 ----- 12 --- 1 --- Yogurt2 -- Peter ---- 1 ------ 21 -- 2 --- Yogurt4 -- Finn  ----- 15 -- Jake ----- 1

I am working with this query and I do not know if I am in the good way. I have tried to use Group by or WHERE Item = Yogurt, but I cannot find a solution to do not get names duplicate

SELECT * FROM TableA as A LEFT OUTER JOIN TableB AS B ON A._id = B._idA

结果是:

身份证 |姓名 |参加 | _id | _idA |元素
1 -- 约翰 ---- 1 ------- 12 --- 1 --- 酸奶
1 -- 约翰 ---- 1 ------- 33 --- 1 --- 酒
2 -- 彼得 --- 1 ------- 21 --- 2 --- 酸奶
2 -- 彼得 --- 1 ------- 35 --- 2 --- 奇巧
4 -- 芬恩 ---- 1
5 -- jack ---- 1

Answer 1

您说您想要条件“Item = Yogurt”，但事实并非如此。
您要查找的结果包含 Item 列具有另一个值 (NULL) 的行。

如果要获取 Finn/Jake 行，则必须编写与这些行匹配的 WHERE 子句:

SELECT *
FROM TableA as A
LEFT OUTER JOIN TableB AS B ON A._id = B._idA
WHERE A.Attend
  AND (B.Item = 'Yogurt' OR B.Item IS NULL);

或者，将此条件放入外部连接子句中，如果没有连接记录，它将被忽略:

SELECT *
FROM TableA as A
LEFT OUTER JOIN TableB AS B ON A._id = B._idA
                           AND B.Item = 'Yogurt'
WHERE A.Attend;