I have this problem with SQLite in Android:
From items I should get:
1) all items with title field containing a string . example '%sho%'
2) check if item belongs to a given project (22)
project
┌───┬─────────┐
│ id│idProject│
├───┼─────────┤
│ 1 │ 1 │
│ 2 │ 22 │
└───┴─────────┘
items projectitems
┌───┬──────┐ ┌───┬─────────┬─────────┐
│id │ title│ │ id│ idItem │idProject│
├───┼──────┤ ├───┼─────────┼─────────┤
│10 │ show1│ │ 1 │ 10 │ 22 │
│11 │ show2│ │ 2 │ 11 │ 22 │
│12 │ show3│ │ 3 │ 12 │ 22 │
│13 │ show4│ │ 4 │ 10 │ 1 │
│14 │ show5│ │ 5 │ 11 │ 1 │
│15 │ show6│ │ 6 │ 12 │ 1 │
└───┴──────┘ └───┴─────────┴─────────┘
I tried this:
SELECT
e.id, e.title, l.idProject
from
items as e
LEFT JOIN projectitems as l
on e.id = l.idItem
WHERE
e.title LIKE '%sho%' AND l.idProject = 22
ORDER BY
e.title ASC
the result I'm getting is:
id title idProject
10 show1 22
11 show2 22
12 show3 22
the result I want:
id title idProject
10 show1 22
11 show2 22
12 show3 22
13 show4 (null)
14 show5 (null)
15 show6 (null)
Also if you know different approaches to accomplish this, let me know. thanks.
Aucun commentaire:
Enregistrer un commentaire