dimanche 14 février 2016

sql left join with condition

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.

sqlfiddle

Aucun commentaire:

Enregistrer un commentaire