Selecting records without relations in another table

[Strahan]

This sounds easy, but I'm having headaches with it.  Maybe I shouldn't be working at 1:20 AM lol.  Anyway, I have two tables.  One is chapters, one is readlog.  I want to pull a list of chapters but only ones I haven't read.  This is the schema:

chapters:
  cid, int, index, autoinc
  id, int, the id of the book
  chapter, varchar, the chapter number
  url, varchar, link to the chapter text

readlog:
  rlid, int, index, autoinc
  uid, int, relates to user table
  cid, int, relates to chapters
  dateread, datetime, self explanatory

So I did:

SELECT chapter,dateadded,dateposted,url FROM chapters c LEFT OUTER JOIN readlog r ON c.cid = r.cid WHERE id = ? AND dateread IS NULL ORDER BY chapter+0

Works great.  Then I realized if my sister goes and reads a chapter of the same book, the query drops that chapter for me too.  I tried adding AND r.uid = ? and pass it my UID, but then I get nothing at all.  Makes sense, because if I'm saying r.uid must be 1 then it only matches chapters I've read (inverse of what I want) but I also say dateread is null so it matches nothing.  I changed it to r.uid <> 1 but that didn't work either. 

My brain is fried, I can't figure out the right way to do this.  Any suggestions?  Thanks!

[kicken]

31 minutes ago, Strahan said:

I tried adding AND r.uid = ?

Add it to your join's ON clause, not the WHERE clause.

[requinix]

Now there's an avatar I haven't seen in a while.

Conditions in an ON are about deciding how stuff in the two tables relate to each other, while conditions in the WHERE are about filtering what you want to see in the results. That's probably not the best way to explain it...
Here you're dealing with readlog.uid. Normally stuff in an ON is for fields in both tables that equal each other, but remember: it's about relationships here. And what that uid really means to the query is "chapters that are not in someone's readlog". In effect, checking the uid is half of the work (the "someone's readlog" part) and the OUTER JOIN + dateread is the other half (the "chapters that are not in" part). Of course, the fact that the second half is being done in outside an ON and actually inside a WHERE is just an unfortunate result of the SQL syntax...

Ah, I'm tired. That probably didn't make any sense and I'm sure someone else could do better.

[Barand]

Simple rule: if you are LEFT JOINing to a table, put any conditions on that table in the ON clause.

(If you put them in the WHERE clause, the join behaves as an INNER JOIN)

[Strahan]

Ahhhh thanks all, that clears it up.  I put a UID criteria in the ON clause and now it properly gives me unread chapters for whatever user is polling.  Nice.

Funny thing is, I'm going to all this effort to build multiuser capability yet I'll probably hardly ever have other people using the site lol.

21 hours ago, requinix said:

Now there's an avatar I haven't seen in a while.

I saw a drawing on DeviantArt by an artist, tidusyuna, of Shirayuki Mizore from Rosario+Vampire and really liked it.  She was my favorite character from the show, and this guy drew her with her long hair before she chopped it off a few eps in.  Decided to use it for an avatar.  Also commissioned him to do a custom piece with her for my BMW's navigation boot screen, lol.