Joining multiple tables

SELECT     tblHistory.dtDate AS Date, tblHistory.dIDNumber AS [ID Number], tblStudent.achName AS [Patron Name], tblMealPlan.achMealPlanName AS [Meal Plan], 
                      tblDevices.achName AS Device, tblTransactionType.achTransactionType AS [Transaction Type], tblHistory.dAmount AS Amount, tblHistory.dBalance AS Balance, 
                      tblHistory.bPass AS Pass
FROM         tblHistory LEFT OUTER JOIN
                      tblDevices ON tblHistory.lDeviceNumber = tblDevices.lDeviceNumber LEFT OUTER JOIN
                      tblTransactionType ON tblHistory.lTransactionType = tblTransactionType.lTransactionType LEFT OUTER JOIN
                      tblMealPlan ON tblHistory.lMealPlan = tblMealPlan.lMealPlanNumber LEFT OUTER JOIN
                      tblStudent ON tblHistory.dIDNumber = tblStudent.dIDNumber
WHERE     (tblHistory.dtDate BETWEEN '1/14/14' AND '2/15/14') AND (NOT (tblStudent.achFax = 'TEST'))

Open in new window


How do i adjust this so that I will get every row from tbl.History.
MillkindAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
magarityConnect With a Mentor Commented:
Better still to just move the student achFax requirement to the join section:

LEFT OUTER JOIN
                      tblStudent ON tblHistory.dIDNumber = tblStudent.dIDNumber
AND (NOT (tblStudent.achFax = 'TEST'))
WHERE     (tblHistory.dtDate BETWEEN '1/14/14' AND '2/15/14')
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
remove your where clause considering dtDate
eg:
SELECT     tblHistory.dtDate AS Date, tblHistory.dIDNumber AS [ID Number], tblStudent.achName AS [Patron Name], tblMealPlan.achMealPlanName AS [Meal Plan], 
                      tblDevices.achName AS Device, tblTransactionType.achTransactionType AS [Transaction Type], tblHistory.dAmount AS Amount, tblHistory.dBalance AS Balance, 
                      tblHistory.bPass AS Pass
FROM         tblHistory LEFT OUTER JOIN
                      tblDevices ON tblHistory.lDeviceNumber = tblDevices.lDeviceNumber LEFT OUTER JOIN
                      tblTransactionType ON tblHistory.lTransactionType = tblTransactionType.lTransactionType LEFT OUTER JOIN
                      tblMealPlan ON tblHistory.lMealPlan = tblMealPlan.lMealPlanNumber LEFT OUTER JOIN
                      tblStudent ON tblHistory.dIDNumber = tblStudent.dIDNumber
WHERE     (NOT (tblStudent.achFax = 'TEST'))

Open in new window

0
 
Scott PletcherSenior DBACommented:
I suspect you want the date restrictions on tblHistory.  But you need to adjust the condition on tblStudent to allow for a NULL value, since values from that table might be missing:


WHERE     (tblHistory.dtDate BETWEEN 20140114' AND '20140215') AND (tblStudent.achFax IS NULL OR NOT (tblStudent.achFax = 'TEST'))
0
All Courses

From novice to tech pro — start learning today.