How to write a sql statement that queries multiple tables

How do you query multiple tables(4 or more)  in mysql server using a join.
These tabs in the workbook represents  tables in a mySQL database, I am trying to write a query that will return the total invoice charges for the state of Illinois for months  January through February and sort it by Customer name I was trying to do a join, how do you go about doing this with a join?

This is what I was thinking

Select C.CustomerName, L.LineAmount
From Customer C  Inner Join Invoice I
ON C.CutomerID = I.CustomerID
Inner Join LineItemCharge L
ON I.InvNbr  = L.InvLineItemID
Inner Join BillTo b
ON L.InvID  = B.InvID
Where I.invdate BETWEEN ‘1/1/2011’ AND ‘2/28/2011’
AND B.BillToState = ‘IL’
ORDER BY CUSTOMER NAME;

Open in new window

SQLQ.xls
DAN2011Asked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
Close, try this:

Select C.CustomerName, sum(L.LineAmount)   <---compute total
From Customer C  Inner Join Invoice I
ON C.CustomerID = I.CustomerID   <--fixed typo here
Inner Join LineItemCharge L
ON I.InvID  = L.InvoiceID     <--this changed
Inner Join BillTo b
ON L.InvID  = B.InvID
Where I.invdate BETWEEN ‘1/1/2011’ AND ‘2/28/2011’    
AND B.BillToState = ‘IL’
group by C.CustomerName                      <--group by customer
ORDER BY CUSTOMER NAME;
0
 
DAN2011Author Commented:
Just to be clear, is it okay to use a inner join with this kind of table or should I use a cross join?
0
 
DAN2011Author Commented:
I meant outer join.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
DAN2011Author Commented:
Sorry for all the short comments I just want to ensure that I am using the joins correctly.
0
 
rubeneConnect With a Mentor Commented:
The difference between an inner join and outer join is that in case of an inner join a row without a matching join condition won't be returned in your output. When using an outer join the row will be returned anyway, in case of a non-match you'll get null values for the joined columns in that row.
0
 
DAN2011Author Commented:
So do you think I should stick with the inner join since I do have matching columns in the table ?And when you say non matching are you referring to tables that don't have related columns?
0
 
Kalyanum Deepak KumarConnect With a Mentor Senior Technical LeadCommented:
1) Inner join should be ok and what DAN2011 said works as per your requirement.
2)Outer joins whether it is right or left should be useful when you want to hareplace the other columns in the select with some other customized values eg: If you want to get the InvoiceId replaced with 100 you could use the query like :

Select C.CustomerName, sum(L.LineAmount),ISNULL(L.InvoiceID,100)   <---compute total
From Customer C  Inner Join Invoice I
ON C.CustomerID = I.CustomerID   <--fixed typo here
LEFT Join LineItemCharge L
ON I.InvID  = L.InvoiceID     <--this changed
Inner Join BillTo b
ON L.InvID  = B.InvID
Where I.invdate BETWEEN ‘1/1/2011’ AND ‘2/28/2011’    
AND B.BillToState = ‘IL’
group by C.CustomerName                      <--group by customer
ORDER BY CUSTOMER NAME;


4)If you perform a left join in the query above, you can get the sum correct, but NULL values can be fetched from the tables when they are not substituted by values as mentioned in point 3 in my post.
0
 
rubeneConnect With a Mentor Commented:
Hi Dan,

By non-matching I mean the situation where there are no matching rows in the joined table. So no rows in the joined table match your ON condition.
If you know (or require) that there are always matching rows then you should indeed use an inner join.
So for example if every invoice has a matching customer row just use an inner join.
0
 
rubeneConnect With a Mentor Commented:
By the way you don't have to specify the INNER keyword specifically in your query, because by default a join will be an inner join. Only if you want to use an outer join you have to specifically say so by using a LEFT JOIN or RIGHT JOIN.
0
 
dqmqConnect With a Mentor Commented:
I can't advise on whether you want inner or outer joins without a better understanding of your data and what results you want.  However, since you are referencing a column from every table, that strongly suggests you don't need to see records for which a row is missing.

For example with inner joins, a customer would drop out if there was no related invoice or billto record.  But that seems OK, since you would not have an acceptable invoice date or billto state if those records were missing.  I don't know if you can have invoices without lineitems, but with an inner join those will drop out, as well.  If you want to see them as $0, then you would need an outer  join to the line item table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.