> the follow query returns 28 records
>
>
> SELECT
> t0.ID, t0.CODICE_SPK,
> t1.ID, t1.CODICE_SPK,
> t2.ID, t2.ISARTICOLO
> FROM AM_GRUPPI t0 JOIN AM_SGRUPPI t1 ON t0.ID =
t1.GRUPPO_ID
> LEFT JOIN AM_FIGURE t2 ON t1.ID = t2.SGRUPPO_ID
> WHERE t0.CODICE_SPK='27' and (t2.id IS NULL or
t2.ISARTICOLO = 0)
>
>
> //---------------------------------------------
> Removing the last table (LEFT JOIN AM_FIGURE), the
query returns 29
> records. One record more than previous query with
left join.
>
>
> SELECT
> t0.ID, t0.CODICE_SPK,
> t1.ID, t1.CODICE_SPK
> FROM AM_GRUPPI t0 JOIN AM_SGRUPPI t1 ON t0.ID =
t1.GRUPPO_ID
> WHERE t0.CODICE_SPK='27'
> I think the first query should return 29 records.
> I have already done a backup/restore cycle before
testing.
> So, I'm only tired or it's a real problem ?
You are tired!
Obviously, the problem is with your data. There is a T1
row which has a T2 which fails the "and (t2.id IS NULL or
t2.ISARTICOLO = 0)" criteria.
To find the problem data, you can:
a) Retry your first query, but drop the T2 criteria --
This will show you the data across all the tables and then
quickly review the data to find the problem row. (Your
last query is not showing the full view of the data)
or
b) Retry your first query, modify your T2 criteria to read
" and (t2.id IS NOT NULL AND t2.ISARTICOLO <> 0)" --
this query should report the 1 row which is being excluded
from the first query.
Sean