* Matt LeBlanc <[EMAIL PROTECTED]> [2006-12-30 08:10]:
> Sorry, I just realized I used the wrong field. Of course, this
> doesn't matter as if the join condition fails, anything from
> hiddenboards is null. Here is a full example of how this would
> work:
>
> CREATE TABLE boards (
> id int,
> active int,
> pos int
> );
>
> INSERT INTO boards VALUES(1,1,1);
> INSERT INTO boards VALUES(2,1,2);
> INSERT INTO boards VALUES(3,1,3);
>
> CREATE TABLE hiddenboards (
> userid int,
> boardId int
> );
>
> INSERT INTO hiddenboards VALUES(1,1); -- not userid 3, so no join
> INSERT INTO hiddenboards VALUES(3,2); -- userid 3, so valid join
> -- no entry for id 3 so no join
>
> SELECT boards.id,CASE WHEN hiddenboards.userid is not null THEN 1 ELSE 0 END
> AS hidden
> FROM boards LEFT JOIN hiddenboards
> ON hiddenboards.userid = 3
> AND hiddenboards.boardid = boards.id
> WHERE boards.active = 1
> ORDER BY pos;
>
> In this particular case, the rows returned are as follows:
>
> id,hidden
> 1,0
> 2,1
> 3,0
You’re abusing the ON clause. You’re not joining on the userid
condition; you’re constraing the result set. You should put it in
the WHERE clause:
SELECT
b.id,
CASE WHEN h.userid IS NOT NULL THEN 1 ELSE 0 END AS hidden
FROM boards b
LEFT JOIN hiddenboards h ON h.boardid = b.id
WHERE
b.active = 1
AND h.userid = 3
ORDER BY b.pos
But this is really an abuse of joining, because you’re not
actually interested in joining data, only in checking, which
should be written as a correlated subquery:
SELECT
b.id,
EXISTS (
SELECT NULL
FROM hiddenboards h
WHERE
h.userid = 3
AND h.boardid = b.id
) AS hidden
FROM boards b
WHERE b.active = 1
ORDER BY b.pos
Note that I use EXISTS and select nothing but NULL to make it
clear that the only fact of interest is whether a matching row
exists in `hiddenboards`.
In that way it is self-documenting what you’re trying to ask for.
Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
