From Join Muilt-tabels Different Joins

[patwhite002]

I'm have this problem were I need to a total line for ever Doctor even if there is no invoices to sum up. I'm sure it all connected to the need to connect more then 2 table on the from ... join statement. Ignore the null columns, they are to match up with the detail list, that are unneeded for the summary rows. when I run this query, I should receive 100 rows, but I'm only receiving 81 and 19 that are missing as zero sales and zero units. It make making a summary page a pure pain because you have to manual match up doctor for each category grouping. If all the rows were there, it would be a 15 min job at best.

select '', Invoices.DoctorNo, rtrim(Doctor.Name) as Doctor_Name, '', '',	   '', '', '',	   '', '',	   '', '', '',	   '', '', '', '',	   Sum(Invoices_Products.ProdAmount), sum(Invoices_Products.ProdQty)  from ((((Doctor   left join Invoices on Invoices.Lab = Doctor.Lab											   and Invoices.DoctorNo = Doctor.Doctor)				   inner join Invoices_Products on Invoices_Products.Lab = Invoices.Lab											   and Invoices_Products.CaseNo = Invoices.CaseNo											   and Invoices_Products.Invoice = Invoices.Invoice)				   inner join Products		  on Products.Lab = Invoices_Products.Lab											   and Products.Product = Invoices_Products.Product)				   inner join Category		  on Category.Lab = Products.Lab											   and Category.Category = Products.Category)  where Doctor.Lab = 1 and ( Doctor.SalesSource = '26' or ( Doctor.SalesSource = '27' and Invoices.Shipped < '2008-03-31' ) ) and Invoices.Shipped > '2008-12-31'	and	( Category.Category = 'DA' or Category.Category = 'HA' or Category.Category = 'GA' or Category.Category = 'WA' )  group by Invoices.DoctorNo, Doctor.Name

[justsomeguy]

Change your inner joins to left joins, a left join will keep all records on the "left" side of the join.

[patwhite002]

Change your inner joins to left joins, a left join will keep all records on the "left" side of the join.

I change the code to as follows:

select '', Invoices.DoctorNo, rtrim(Doctor.Name) as Doctor_Name, '', '',	   '', '', '',	   '', '',	   '', '', '',	   '', '', '', '',	   Sum(Invoices_Products.ProdAmount), sum(Invoices_Products.ProdQty)  from ((((Doctor   left join Invoices			on Invoices.Lab = Doctor.Lab											   and Invoices.DoctorNo = Doctor.Doctor)					left join Invoices_Products on Invoices_Products.Lab = Invoices.Lab											   and Invoices_Products.CaseNo = Invoices.CaseNo											   and Invoices_Products.Invoice = Invoices.Invoice)					left join Products		  on Products.Lab = Invoices_Products.Lab											   and Products.Product = Invoices_Products.Product)					left join Category		  on Category.Lab = Products.Lab											   and Category.Category = Products.Category)  where Doctor.Lab = 1 and ( Doctor.SalesSource = '26' or ( Doctor.SalesSource = '27' and Invoices.Shipped < '2008-03-31' ) ) and Invoices.Shipped > '2008-12-31'	and	( Category.Category = 'DA' or Category.Category = 'HA' or Category.Category = 'GA' or Category.Category = 'WA' )  group by Invoices.DoctorNo, Doctor.Name

After I switch to left join's, 81 row is what I received Still missing 19 rows.

[justsomeguy]

I've always just typed the code box, e.g.:

code goes here

Are there 100 records in the Doctor table? Is one of the WHERE conditions filtering out the other records?

[patwhite002]

I've always just typed the code box, e.g.:

code goes here

Are there 100 records in the Doctor table? Is one of the WHERE conditions filtering out the other records?

No, are 1,387 doctors in their database between their 2 laboratories. I came up with the count from a Excel worksheet where I took the 12 query I wrote, loaded them onto a tab for each query. Then spending 4 hr matching up the all the doctors between all the tabs and inserting the missing doctors into each tab, I came up with a combind list of doctors. No one single list had all the doctors on it. It take no time at all to create the Category By Doctor Summary sheet if all sheets have matching Doctor lists. I get one category query working then I can get all the category queries working. Everyone is happy.

[justsomeguy]

I suspect that the WHERE conditions are eliminating the other records. For example, this condition will eliminate all rows that do not have an invoice:Invoices.Shipped > '2008-12-31'That will only show rows that do have an invoice, with a date matching that condition. You may need to add other conditions where the invoices.shipped is either null, or it needs to meet the criteria. As it is, requiring the invoice date to be after a certain date will remove all records with no invoice date.

[patwhite002]

I suspect that the WHERE conditions are eliminating the other records. For example, this condition will eliminate all rows that do not have an invoice:Invoices.Shipped > '2008-12-31'That will only show rows that do have an invoice, with a date matching that condition. You may need to add other conditions where the invoices.shipped is either null, or it needs to meet the criteria. As it is, requiring the invoice date to be after a certain date will remove all records with no invoice date.

Are you tell me, that sql is creating the rows then uses the where to filter out the output stream? I tried ( Invoices.Shipped > '2008-12-31' or Invoices.Shipped = '0' ) but that give me a "Invalid date, time or timestamp value"

[justsomeguy]

Are you tell me, that sql is creating the rows then uses the where to filter out the output stream?

Pretty much, all of the joins create a large record set, and then the WHERE conditions filter records out of the set to only return the records that match.

I tried ( Invoices.Shipped > '2008-12-31' or Invoices.Shipped = '0' ) but that give me a "Invalid date, time or timestamp value"

There is special syntax for checking null values. With a left join, if you have a record (like a doctor) that doesn't have any matches in the joined table (like the invoices), it will use NULL for the missing fields. In MySQL you can use IS [NOT] NULL, e.g. Invoices.Shipped > '2008-12-31' or Invoices.Shipped IS NULL )For MS SQL Server, the syntax is a little more strange:( Invoices.Shipped > '2008-12-31' or IS_NULL(Invoices.Shipped, '2000-01-01') = '2000-01-01' )that could be shortened to this:( IS_NULL(Invoices.Shipped, '2000-01-01') > '2008-12-31' )

[patwhite002]

You was almost 100% correct. It was the Null Invoices.shipped but the Category also played into this. It too need to be able to pass a Null item. But that created new problems. Then I remember that inorder to get the category table you have to have the product table. I switch the Category to Prodcuts add the null test and Bing Bang! Now I'm getting 233 rows. I added the SalePerson and SaleSource to make sure I wasn't gettign data I didn't want.Is there a way to added comments to the SQL? Nothing like adding some notes to remind me what it take to make it work.

select '', Doctor.Doctor, rtrim(Doctor.Name) as Doctor_Name, Doctor.SalesPerson, Doctor.SalesSource,	   '', '', '',	   '', '',	   '', '', '',	   '', '', '', '',	   Sum(Invoices_Products.ProdAmount), sum(Invoices_Products.ProdQty)  from Doctor   left join Invoices			on Invoices.Lab = Doctor.Lab										   and Invoices.DoctorNo = Doctor.Doctor				left join Invoices_Products on Invoices_Products.Lab = Invoices.Lab										   and Invoices_Products.CaseNo = Invoices.CaseNo										   and Invoices_Products.Invoice = Invoices.Invoice				left join Products		  on Products.Lab = Invoices_Products.Lab										   and Products.Product = Invoices_Products.Product				left join Category		  on Category.Lab = Products.Lab										   and Category.Category = Products.Category  where Doctor.Lab = 1 and ( Doctor.SalesSource = '26' or ( Doctor.SalesSource = '27' and Invoices.Shipped < '2008-03-31' ) )	and ( Invoices.Shipped > '2008-12-31' or Invoices.Shipped is null )	and	( Products.Category = 'DA' or Products.Category = 'HA' or Products.Category = 'GA' or Products.Category = 'WA' or Products.Category is null)  group by Doctor.Doctor, Doctor.Name, Doctor.SalesPerson, Doctor.SalesSource

The client say the about 100 doctor in "26" and 25 in "27". now I looked at the output and 90% of the doctor are in "26". Someone else going to look at the doctors.It the test that will get you everyrime.Thanks for gettng me pointed in the right direction.

[justsomeguy]

I've only used comments with MS SQL Server, you start a line with "--" to put a comment on that line. MySQL comments are described here:http://dev.mysql.com/doc/refman/5.0/en/comments.html