Bit of confusion with a basic join

Here is the MySQL query I am using:

SELECT * FROM wc_poems p, wc_poem_cat_link l WHERE p.id=l.poem_id AND (l.cat_id IN (3) OR p.all_cats=1) ORDER BY p.list_order ASC

The query should return a column if all_cats is set to 1, no matter whether the other conditions are met or not. But it only returns if cat_id matches the condition (i.e. it is 3).

Using EXPLAIN I get the message “Impossible WHERE noticed after reading const table…” I though I had some idea of what that meant, but can’t put my finger on it.

If somebody could help I would appreciate it. If there is some more information I need to post please let me know.

i’m going to guess that a poem which has all_cats=1 isn’t going to be linked to any wc_poem_cat_link rows, so the inner join is messing things up

SELECT id
     , list_order
  FROM wc_poems 
 WHERE all_cats = 1
    OR EXISTS
       ( SELECT 937
           FROM wc_poem_cat_link 
          WHERE poem_id = wc_poems.id
            AND cat_id = 3 )
ORDER 
    BY list_order

Let me rewrite that for you:


SELECT
   something
 , anything
 , just_not_the_dreaded_star
FROM
  wc_poems p
     INNER JOIN
  wc_poem_cat_link l
     ON
  p.id=l.poem_id
WHERE
   l.cat_id=3
      OR
   p.all_cats=1
ORDER BY
   p.list_order ASC

Does that do what you want?

Fantastic help guys, as ever!