> 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