CODEWITHSUNDEEP
sqlite
Alsmayer
Alsmayer

Reputation: 256

SQLite: Query not responding when key = null

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.

Upvotes: 0

Views: 58

Answers (1)

Alsmayer
Alsmayer

Reputation: 256

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

Upvotes: 1

Related Questions