Hi,
I have this problem, I want to make report using PivotTable/CrossTab.
The problem is, I want so show NULL value to.

This is my query to get the data (I'm using an application that can
make pivottable from the data AND MySQL 5)
SELECT I.invoice_number, I.date,P.id, P.name, D.quantity
FROM products AS P LEFT JOIN (
sales AS I INNER JOIN sales_details AS D ON I.id = D.sale_id )
ON P.id = D.product_id
WHERE I.sales_person_id = '1'
AND I.date
BETWEEN CAST( '2007-01-01 00:00:00' AS Datetime )
AND CAST( '2007-06-30 23:59:59' AS Datetime )
;
The product has 10 rows.
The sales record has 2 row that satisfied the criteria on sub query.
and in sales_details in one sales id it has 2 record and on the other
sales is it has 3 record

The above query give 5 record.

But I want a query that give me 20 record, with 15 records with null
value (because 2 sales record with 10 products on each sales record).

I try to use this query
SELECT I.date, P.id, P.name, D.quantity
FROM products AS P LEFT JOIN sales_details AS D ON P.id =
D.product_id,sales AS I
WHERE I.id IN (
SELECT I.id
FROM sales I
WHERE I.sales_person_id='1' AND
I.date BETWEEN CAST( '2007-01-01 00:00:00' AS Datetime )
AND CAST( '2007-06-30 23:59:59' AS Datetime )
)
;

It give me 26 record with.

Help please.

TIA (Thanks In Advance) )

How about this?

SELECT I.invoice_number, I.date, P.id, P.name, D.quantity
FROM products AS P
INNER JOIN
sales AS I
ON I.sales_person_id = '1'
AND I.date
BETWEEN CAST( '2007-01-01 00:00:00' AS Datetime )
AND CAST( '2007-06-30 23:59:59' AS Datetime )
LEFT JOIN
sales_details AS D
ON I.id = D.sale_id
AND P.id = D.product_id
;

I think that your second example dropped the condition "I.id =
D.sale_id", then extra rows were included in the result.

Thanks Tonkuma, your query work find.
And I'm also has another answer
I use crossjoin .