After much experimenting I have come up with an SQL statement that does the job for me. What would be nice would be to make it more elegant as the repeating JOIN offends my sense of tidiness. I have had no success. Can anyone suggest an improvement?
SELECT a.ClubArmsID, a.ArmsID, a.CalibreID, a.PowderID, l1.GListItem AS ArmType, l2.GListOther AS ArmSort, l2.GListItem AS ArmCalibre, l3.GListItem AS ArmPowder FROM steel_arms a JOIN (SELECT * FROM globallists WHERE GListDeleted = 0 AND (GListName = 'Arms' OR GListName = 'Calibre' OR GListName = 'Powder') UNION SELECT * FROM steel_lists WHERE ListDeleted = 0 AND (ListName = 'Arms' OR ListName = 'Calibre' OR ListName = 'Powder') ) l1 ON l1.GListID = a.ArmsID JOIN (SELECT * FROM globallists WHERE GListDeleted = 0 AND (GListName = 'Arms' OR GListName = 'Calibre' OR GListName = 'Powder') UNION SELECT * FROM steel_lists WHERE ListDeleted = 0 AND (ListName = 'Arms' OR ListName = 'Calibre' OR ListName = 'Powder') ) l2 ON l2.GListID = a.CalibreID JOIN (SELECT * FROM globallists WHERE GListDeleted = 0 AND (GListName = 'Arms' OR GListName = 'Calibre' OR GListName = 'Powder') UNION SELECT * FROM steel_lists WHERE ListDeleted = 0 AND (ListName = 'Arms' OR ListName = 'Calibre' OR ListName = 'Powder') ) l3 ON l3.GListID = a.PowderID ORDER BY ArmType ASC, ArmSort ASC, ArmCalibre ASC Charlie [Non-text portions of this message have been removed]
