After all the great help from this forum, I have got the query to work. Thanks Peter. On 60,000 records however the server times out, so now I need to look for efficiency.
There is a common sub-expression.
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0)
Can I rely on the optimizer to recognise it, can I convert it to an expression (I don't know how to use expressions), or is there a way to help the optimiser recognise it? All I have is phpMyAdmin, so not much in the way of diagnostic tools.
SELECT CALLSIGN,
c.Entity,
QSO_DATE,
TIME_ON,
BAND,
MODE,
CASE
WHEN MODE='CW' THEN 'CW'
WHEN MODE IN ('AM',
'SSB',
'FM') THEN 'Phone'
WHEN NOT MODE IN ('DSTAR',
'VOI') THEN 'Digital'
ELSE 'Other'
END AS MDA,
CASE NOT EXISTS
(SELECT *
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0))
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NC,
CASE NOT EXISTS
(SELECT *,
CASE
WHEN MODE='CW' THEN 'CW'
WHEN MODE IN ('AM',
'SSB',
'FM') THEN 'Phone'
WHEN NOT MODE IN ('DSTAR',
'VOI') THEN 'Digital'
ELSE 'Other'
END AS MDB
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0) HAVING MDA=MDB)
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NM,
CASE NOT EXISTS
(SELECT *
FROM QSO AS b
WHERE a.DXCC=b.DXCC
AND a.BAND=b.BAND
AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0))
WHEN TRUE THEN 'Y'
ELSE 'N'
END AS NB
FROM QSO AS a
JOIN DXCC AS c ON a.DXCC=c.DXCCnum
WHERE ClientID='20005881'
AND find_in_set('QSL_RCVD',FLAGS)>0
AND find_in_set('QSL_OK',FLAGS)=0
AND find_in_set('EQSL_OK',FLAGS)=0
AND find_in_set('LOTW_OK',FLAGS)=0
HAVING NC='Y'
OR NB='Y'
OR NM='Y'
ORDER BY Entity