Solved

Joining multiple tables

Posted on 2014-02-10
3
146 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 39

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:ScottPletcher
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now