[thelist] mySQL query

[john at johnallsopp.co.uk]

Hi

I'm struggling with an SQL query. I have a table that contains one or
two category ids which are foreign keys. I want to see if a string is
contained in the category description. The problem is that sometimes
the second category id is blank. Not null, blank. So while this:
SELECT IT_D_id, IT_D_des, IT_D_wp, IT_D_wn, IT_D_s FROM `IT_D` id,
`CAT` ca WHERE id.CA_id1 = ca.CA_id  and ca.CAT_des='Chemical' order
by IT_D_id desc limit 0,100
gives the correct 5

this:
SELECT IT_D_id, IT_D_des, IT_D_wp, IT_D_wn, IT_D_s FROM `IT_D` id,
`CAT` ca, `CAT` ca2 WHERE id.CAT_id1 = ca.CAT_id and id.CAT_id2 =
ca2.CAT_id and (ca.CAT_des='Chemical' or ca2.CAT_des='Chemical') order
by IT_D_id desc limit 0,100
gives an incorrect 4, the one that's missing is the one where
id.CAT_id2 = ""

I'm using mySQL, so no subselections are allowed. So I'm heading into
temporary tables. Here's what I have so far:

CREATE TEMPORARY TABLE tmp TYPE=HEAP SELECT IT_D_id FROM `IT_D` id,
`CAT` ca2 WHERE NOT id.CAT_id2 = "" and id.CAT_id2 = ca2.CAT_id and
ca2.CAT_description='Chemical' limit 0,100;

I'm hoping this will put into tmp a set of IT_D_ids where the category
id isn't blank, and the category description contains the word
"Chemical".

SELECT id.IT_D_id, IT_D_des, IT_D_wp, IT_D_wn, IT_D_s FROM `IT_D` id,
`CAT` ca, `CAT` ca2, tmp WHERE (id.CAT_id1 = ca.CAT_id and
ca.CAT_des='Chemical') or id.IT_D_id = tmp.IT_D_id order by IT_D_id
desc limit 0,983;

I'm hoping this looks for "Chemical" in the first category description
and adds the results to those from the first

DROP TABLE tmp;

The problem is, it simply doesn't seem to work. It runs in phpMyAdmin,
but it looks like it doesn't select anything. That's been true since I
started using a temporary table. Even this
CREATE TEMPORARY TABLE tmp TYPE=HEAP SELECT IT_D_id FROM `IT_D` id,
`CAT` ca2 WHERE id.CAT_id2 = ca2.CAT_id limit 0,100;
select * from tmp where 1;
looks like it returns nothing where it really should.

Any clues where I might be going wrong with this?

J