Thanks! My ego is suitably massaged!
I've spent a lot of time working on relational databases and I always looked for ways to use SQL to do new things.
I'll try and anotate it for you....
This bit simply gets the information that you are interested in. The IFNULL function just checks the returned value of iRelate.relateType, if it is NULL (i.e. not found) then it returns NO.
SELECT iName.nameCode, iName.nameTitle, IFNULL(iRelate.relateType, "NO"), iRelType.typeTO
The FROM is just saying which tables the data comes from, that's simple enough. But the LEFT OUTER JOIN isn't so simple. All it does is says for every row found on iName, try and find a row on iRelate where iName.nameCode and iRelate.relateItem match. If a match is found, then the JOIN is successful and all the data is returned. If a match is not found, the JOIN is unsuccessful but it still returns the row from iName whereas a normal join wouldn't.
FROM iName LEFT OUTER JOIN iRelate
ON iName.nameCode = iRelate.relateItem
The LEFT JOIN brings in the last table to the query and this is a normal join between iRelate and iRelType where iRelate.relateType = iRelType.typeID
LEFT JOIN iRelType
ON iRelate.relateType = iRelType.typeID
I hope that is reasonaby clear.
The IFNULL() function doesn't do exactly what I wanted. I wanted something that said "if it's null, return 'NO', else return 'YES'" but I couldn't find it. There's a function in ORACLE called DECODE which does this, but MySQL's DECODE is something to do with encryption !!!
You should have a look at MySQL's website. The documentation is online. If you look through there is a whole chapter on SELECT and query syntax. Not exactly enthralling but you'll find some pretty neat tricks.
