The Select statement in your View (edited for readability) ...
CREATE VIEW ldz_from_rec AS
SELECT
l.id AS id,
p.Region AS region,
l.LDZ_Id AS LDZ_Id,
l.LDZ_Exit AS LDZ_Exit
FROM gas_postcode p
JOIN gas_postcode_ldz l ON p.Postcode = LEFT(l.Postcode, 2)
GROUP BY p.Region, l.LDZ_Id, l.LDZ_Exit ;
returns an empty set, so this dataset can't be used to debug the problem query.
There's a more basic problem with the View on which the query is based. Against SQL standards, MySQL allows non-aggregating, non-aggregated columns in Group By queries. Such column results are accurate, however, if and only if there is a unique value of the non-aggregating, non-aggregated column(s) for each unique Group By combination. When that's the case, results in the non-aggregating, non-aggregated column are accurate, but when it isn't, they are arbitrary. We're talking about the ID column. So: what is Group By supposed to achieve in that View?
The query effectively fetches such IDs from a join of this problematic View with a Select Distinct Region subset of itself, hiding the join in a correlated subquery. Which is why I say the query is funky.
To find out if the problematic Group By is the source of the XOR-OR anomaly, lose the Group By in the View in favour of a Select Distinct.
If that doesn't fix the problem, find out if the funky correlated subquery pseudo-join is the source of the problem by rewriting it as a conventional join.
And to further investigate what's going on, inspect the results of ...
select
(select region from ldz_from_rec order by ldz_exit asc limit 1) as ldz_from_rec_region,
distinct1.region as distinct_region
from ( SELECT DISTINCT region FROM ldz_from_rec ) as Distinct1 ;
Edited 2 time(s). Last edit at 08/16/2016 01:19PM by Peter Brawley.