Computer Programmer <[EMAIL PROTECTED]> wrote on 05/20/2005 07:56:39 AM:
> SELECT t2.`col1`, t3.`title`, t3.`col3`, t4.`published` FROM t1 LEFT
JOIN
> t2 ON (t2.`catid`=t1.`id`) LEFT JOIN t3 ON (t3.`id`=t2.`sid`) CROSS JOIN
> `main_data` t4 ON (t4.`id`=t2.`sid`) WHERE t2.`catid`='7' AND
> t4.`published`=1 GROUP BY `sid` ORDER BY t3.`title`;
>
> What this does is to show all the titles under catid ("Category ID")
that
> is published.
>
> 0 = not publish
> 1 = publish
>
> The problem is, even if the `published` column is 0, it will still show
the
> title.
>
> I tried to do the reverse, `published`=0, and it will still show
everything
> instead of just showing the unpublished titles.
>
> I'm using MySQL 4.1.12 under Windows XP.
>
>
Since you need to have a value from t4, that means that you need to have a
row from t2 (or the linkage between t1 and t4 will not exist). What if you
changed your JOIN to t2 to an INNER (CROSS) JOIN and do the same with t4?
You were going to restrict your results to only those rows where
t4.published=1 with your WHERE clause anyway, might as well restrict our
results to just those rows where that value is possible.
I indented the t4 JOIN so that it would be more apparent that it is a
child table of the JOIN with t2 and not directly joined to t1.
SELECT t2.`col1`, t3.`title`, t3.`col3`, t4.`published`
FROM t1
INNER JOIN t2
ON (t2.`catid`=t1.`id`)
INNER JOIN `main_data` t4
ON (t4.`id`=t2.`sid`)
LEFT JOIN t3
ON (t3.`id`=t2.`sid`)
WHERE t2.`catid`='7'
AND t4.`published`=1
GROUP BY `sid`
ORDER BY t3.`title`;
Do my changes make sense or were you trying to find something else? The
other thing to try is to make the JOIN with t4 another LEFT JOIN. Your
where clause will wind up making it work like an INNER anyway.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
PS. There are a few unresolved bugs involving JOINS. Things like "LEFT
then INNER" or "LEFT, RIGHT, and INNER" in particular sequences seem to be
problematic. Your "join malfunction" is probably covered by one of them.
