Re: MYSQL left joins

[Peter Brawley]

Okeh, I understand your first query ...

SELECT categories.*
FROM categories
LEFT JOIN categories AS catwo ON categories.Reference = catwo.Reference
WHERE categories.CATEGORY = 'ACCOUNT';

... but your "improvement" ...

SELECT ca.*
FROM categories ca
LEFT JOIN categories catwo USING Reference
WHERE ca.CATEGORY eq 'ACCOUNT'

... isn't MySQL syntax AFAIK. I'll assume you mean ...

SELECT ca.*
FROM categories ca
LEFT JOIN categories catwo USING (Reference)
WHERE ca.category='ACCOUNT';

... which I'd expect to repeat all ca rows with category='account' plus an extra row for each row where there's a dupe `reference` value. How that could help you search, I've not the foggiest.

I'll have a lash at feeding your requirement back to you in language that I can understand ... you want to find rows where category='account', specifically the reference values in those rows, and list all the other ca.category values where reference=1. Is that it? If not, please elaborate.

Meanwhile your note that "The Checkcode field is a unique field combination of reference number and category" flags a serious redundancy in the table design---the concatenated field adds zero information, the column should be removed. To enforce uniqueness on those two columns, just define one Unique index on them.