0

I have the following tables

R: 
rid, wid, sid, attend
1    1    3     1
2    1    2     0
3    2    3     1
4    3    1     0
5    2    1     1
6    4    1     1

E: 
eid, wid,sid
1    1    3
2    2    1

W:
wid, title
1    title1
2    title2
3    title3
4    title4

I want to retrieve the title of W where the wid is in R but not in E. Naturally, I will use LEFT OUTER JOIN. I wrote the following query

SELECT  DISTINCT  w.title
FROM E LEFT OUTER JOIN  R 
ON R.sid = E.sid  AND R.wid = E.wid  
JOIN W
ON R.wid = W.wid
WHERE R.sid = 1  AND R.attend = 1

this will return the titles of wid that exists in both tables R and E: title2 and title3. However, I want to retrieve the titles of wid that exists in R but not in E i.e: title4. Therefore, when I LEFT OUTER JOIN R with E, the columns of E that does not have matching values in R will be filled with NULL values -as far as I know-. Though, when I use the clause WHERE E.sid = NULL or ON E.sid = NULL the query does not retrieve anything what so ever. I tried to retrieve from the table with simple query like SELECT * FROM E where sid = NULL but it would not retrieve anything although I added a row with sid = null just to test. so, maybe there is a problem with SQLite supporting null values or maybe it is just something in my query.

I have been searching for a week now. I hope I can find some help here as I usually do.

Alsmayer
  • 236
  • 1
  • 4
  • 13
  • Look at http://stackoverflow.com/questions/7519621/where-is-null-not-working-in-sqlite if it helps. See also www.techonthenet.com/sqlite/is_null.php – AFract May 10 '15 at 07:17
  • possible duplicate of [Is there any difference between IS NULL and =NULL](http://stackoverflow.com/questions/3777230/is-there-any-difference-between-is-null-and-null) – Joe May 10 '15 at 07:35
  • 1
    THANKS @AFract , the first link helped me. I am going to post my answer.. @ Joe thanks to you too – Alsmayer May 10 '15 at 07:57

1 Answers1

1

the first link that @AFract provided helped me. I had two problems.

  1. I was putting table E on the left hand side of the LEFT OUTER JOIN and table R on the right, which does not give proper output. I had to switch their positions
  2. apparently the syntax E.sid = NULL does not work for SQLite although it is written in their documentation on the official site. the correct syntax that worked for me is E.sid IS NULL

so I modified my query as following

SELECT  DISTINCT W.title
FROM R  LEFT OUTER JOIN E
ON R.sid = E.sid  AND R.wid = E.wid  JOIN W ON R.wid = W.wid
WHERE R.sid = 1 AND E.sid IS NULL AND R.attend = 1
Alsmayer
  • 236
  • 1
  • 4
  • 13