• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

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
0
DAN2011
Asked:
DAN2011
  • 4
  • 3
  • 2
  • +1
6 Solutions
 
dqmqCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
DAN2011Author Commented:
Sorry for all the short comments I just want to ensure that I am using the joins correctly.
0
 
rubeneCommented:
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 KumarCommented:
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
 
rubeneCommented:
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
 
rubeneCommented:
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
 
dqmqCommented:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now