On Fri, Nov 22, 2002 at 08:13:46PM -0500, Philip Mak wrote:
> SELECT *
> FROM boards
> LEFT JOIN boardAdmins
> ON boardAdmins.userId = #{userId}
> AND boardAdmins.boardId = boards.id
> LEFT JOIN boardMembers
> ON boardMembers.userId = #{userId}
> AND boardMembers.boardId = boards.id
> AND boards.id = #{boardId}
Part of your problem is that you're not quite normalized; if you had
a table "boardUsers" with a SET field of "admins|members" then it would
be easier to show ... however, I'd do:
SELECT *
FROM boards
LEFT JOIN boardAdmins
ON boardAdmins.boardId = boards.id
LEFT JOIN boardMembers
ON boardMembers.boardId = boards.id
WHERE boards.id = #{boardId}
AND boardAdmins.userId = #{userId}
AND boardMembers.userId = #{userId}
See how the ON clauses in the QUERY all refer the current table back
to the previous table? I'm not sure about parentheses, but this should
work (untested).
--
Michael T. Babcock
CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc)
http://www.fibrespeed.net/~mbabcock/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
