Exclusion from LEFT JOIN?

[dalecosp]

This is probably a FAQ for you guys, but I'm not having much luck with the board's search feature at present.

Here's the jist of it:

mysql>select gc.id,gc.title,gce.name,gce.value,gce.question as qid,gc.live 
from good_cookies as gc left join good_cookies_extra as gce on (gc.id = gce.class_id) and (gc.live=1) limit 5;
+-----+------------------------------------------------+-------+-------+------+------+
| id  | title                                          | name  | value | qid  | live |
+-----+------------------------------------------------+-------+-------+------+------+
| 151 | White Chip Macadamia Nut                       | NULL  | NULL  | NULL |    0 |
| 146 | Chocolate Chip                                 | NULL  | NULL  | NULL |    0 |
| 195 | Pecan Sandies                                  | Brand | Ricoh |  310 |    1 |
| 198 | Rocky Road                                     | Brand | Ricoh |  310 |    1 |
| 147 | Oatmeal Raisin Wunderschnitzel                 | NULL  | NULL  | NULL |    0 |
+-----+------------------------------------------------+-------+-------+------+------+

I don't mind the NULLs (after all, that's why the LEFT JOIN), but I can't wrap my head around only getting results with live=1 ... 🙁

I've spent a tad too much time trying to read up today and my brain's starting to smoke; I thought perhaps one of you DBA wonder-types would have a Good Word for me 🙂

Thank you!

[dalecosp]

Blargh. Got it. Replace "and" with "where":

mysql>select gc.id,gc.title,gce.name,gce.value,gce.question as qid,gc.live 
from good_cookies as gc left join good_cookies_extra as gce on (gc.id = gce.class_id) where (gc.live=1) limit 5;
+-----+------------------------------------------------+-------+-------+------+------+
| id  | title                                          | name  | value | qid  | live |
+-----+------------------------------------------------+-------+-------+------+------+
| 151 | White Chip Macadamia Nut                       | NULL  | NULL  | NULL |    0 |
| 146 | Chocolate Chip                                 | NULL  | NULL  | NULL |    0 |
| 195 | Pecan Sandies                                  | Brand | Ricoh |  310 |    1 |
| 198 | Rocky Road                                     | Brand | Ricoh |  310 |    1 |
| 147 | Oatmeal Raisin Wunderschnitzel                 | NULL  | NULL  | NULL |    0 |
+-----+------------------------------------------------+-------+-------+------+------+

[Derokorian]

You know I didn't even notice that and was part of a join clause heh. I was like well he already has live=1 in the where clause so I have no idea why its showing up. ALTHOUGH the result you just posted still shows 5 records 3 with live = 0 and 2 with live = 1 so I imagine there was a copy paste error or you didn't really get it?