MySQL, how can I SELECT WHERE one field value doesn't occurs in another?
- by Jules
I'm trying to do a search on my test server, speed isn't so important. I want to find if a phrase, the whole contents of one field, occurs in any part (as a whole) doesn't occur in another field.
Heres my current query which finds if the phrase does occur
SELECT pads.padid, pads.programname, keywords.word
FROM Pads JOIN keywords ON Pads.Padid = keywords.Padid
WHERE pads.Programname = keywords.word
AND RemoveMeDate = '2001-01-01 00:00:00'
My problem is that the field word contains other words too.
I need to produce a count of where there isn't a keywords record with keywords.word equal to pads.programname ?
EDIT:
So if I have these records
Pads:
PadID=3 ProgramName=my prog
PadID=4 ProgramName=Bad prog
PadID=5 ProgramName=Good prog
PadID=6 ProgramName=other prog
Keywords:
PadID=3 word=fox prog
PadID=3 word=pig prog
PadID=4 word=big prog
PadID=4 word=nice prog
PadID=5 word=fox prog
PadID=6 word=bad prog
PadID=6 word=fox prog
The query I want would retun a count of 3 and record 6 has bad prog