Solved

Joining multiple tables

Posted on 2014-02-10
3
148 Views
Last Modified: 2014-02-11
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.
0
Comment
Question by:Millkind
3 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39848611
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39848720
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
 
LVL 13

Accepted Solution

by:
magarity earned 500 total points
ID: 39848773
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question