Grouping Problem.
I have two tables that have a master-detail relationship. For the purposes of this posting, I will call them the Customers table and the Orders table.
The Customers table has a primary key called CustomerID, and the Orders table has a primary key called OrderID, and a foreign key called CustomerID. Naturally, the one-to-many relationship is via CustomerID.
I have a query that pulls in the data from the Customers table, and from the Orders table using an inner join. Records are ordered by CustomerID.
In the report's Group Header, data is grouped by CustomerID. The customer data fields are in the Group Header, and the order data fields are in a Master Data band under the Group Header.
It all works fine. If one or more customer records are selected, the appropriate order data is also displayed for each respective customer.
But here's the problem...
If a customer record has no orders, *nothing* is displayed, ie. the report is totally blank. And if a combination of records are selected, ie. customers with and without orders, only the ones with orders are displayed. The others just don't appear in the report.
I have been trying to resolve this problem for a week now without any success. What could I be doing wrong?
Thanks.
Regards,
Steve
The Customers table has a primary key called CustomerID, and the Orders table has a primary key called OrderID, and a foreign key called CustomerID. Naturally, the one-to-many relationship is via CustomerID.
I have a query that pulls in the data from the Customers table, and from the Orders table using an inner join. Records are ordered by CustomerID.
In the report's Group Header, data is grouped by CustomerID. The customer data fields are in the Group Header, and the order data fields are in a Master Data band under the Group Header.
It all works fine. If one or more customer records are selected, the appropriate order data is also displayed for each respective customer.
But here's the problem...
If a customer record has no orders, *nothing* is displayed, ie. the report is totally blank. And if a combination of records are selected, ie. customers with and without orders, only the ones with orders are displayed. The others just don't appear in the report.
I have been trying to resolve this problem for a week now without any success. What could I be doing wrong?
Thanks.
Regards,
Steve
Comments
it should look something like
select * from customer a, orders b, items c, parts d
where a.custno = b.custno
and b.orderno = c.orderno
and c.partno = d.partno
order by a.company, b.orderno
the key is the second line you only select from customer where orders exist.
in other words your join is incorrect.
New style coding (SQL2003) which is easier to read, uses joins but you must use a LEFT OUTER JOIN in your case
select * from customer a
LEFT OUTER JOIN orders b
where a.custno = <condition> [and b.orderno = <condition>]
order by a.company, b.orderno
If a proper SQL database is used then as far as this query is concerned primary and foreign keys are totally irrelevant. IOW, even is there are no keys or indexes whatsoever, the query result would be correct albeit slower.
PS The years mentioned above may not be quite correct
the point was the last line of my response
his join was incorrect.
I'm such an idiot. The answer was so obvious - I couldn't see the forest for the trees! I replaced the INNER JOIN with a LEFT OUTER JOIN and it now works perfectly. I don't know how I missed that. [img]style_emoticons/<#EMO_DIR#>/huh.gif" style="vertical-align:middle" emoid=":huh:" border="0" alt="huh.gif" /> Thanks for your help. Regards, Steve'[/img]