Computer Programmer wrote:
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.
It's a good idea to take the time to reformat your query to make it
easily readable. That helps us to help you. (Sometimes, you may even
see the problem yourself as a result.) Your query reformatted:
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;
Shawn has already posted an analysis of some problems here. Let me add
to his answer.
First, it is unclear why table t1 is part of this query, as its presence
adds nothing. No column from t1 is selected or used. The only
possibility is that you intended to get results for rows in t1 which
don't have corresponding rows in t2, but those are thrown away by
subsequent joins and the WHERE clause.
Second, you seem to be misusing GROUP BY. You are grouping on sid, but
you are selecting t2.col1, t3.title, t3.col3, and t4.published. When
you use GROUP BY, it usually only makes sense to select grouped columns
and aggregate functions. Indeed, many systems wouldn't allow your query
because of the non-grouped columns in the SELECT clause. MySQL allows
selecting non-grouped columns, but you are warned not to do so unless
the non-grouped columns are guaranteed to have groupwise unique values.
Otherwise, you get nonsense results. I think it's possible there's a
flaw in the logic of your joins which is obfuscated by the grouping on sid.
Here's a substitute query:
SELECT t2.col1, t3.title, t3.col3, t4.published
FROM main_data t4
JOIN t2 ON t2.sid=t4.id
LEFT JOIN t3 ON t3.id=t2.sid
WHERE t2.catid=7
AND t4.published=1
ORDER BY sid, t3.title;
I left out table t1, rearranged the order of the joins, and dropped the
grouping on sid. Instead of grouping on sid, I collect all the rows
with the same sid by adding sid to the ORDER BY. If this returns
expected results, then there's a good chance that the "{RIGHT} JOIN
after a LEFT JOIN" bug is part of the problem, as Shawn suggests, but if
this produces unexpected results, then you (also) have a logic problem.
Of course, all this is based on the query you gave us, which I'm
guessing isn't your real query, unless you actually have tables named
t1, t2, t3, and t4, with columns named col1 and col3. It may be that
the problems I've found stem from the translation of your query to the
one you gave us.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
