January 5, 2017 at 6:19 am
In the query below, the relationship between the tables tblDataPermit and tblDataFees is the application number. If the application number in tblDataPermit does not appear in tblDateFees then the entire item does not appear in the query result. I still need the tblDataPermit data to appear, but with the tblDateFees column blank or zero.
>>SELECT
tblDataPermit.ApplicationNumber
,tblDataPermit.PermitNumber
,tblDataPermit.Type
,tblDataPermit.Status
,tblDataPermit.ApplicationType
,tblDataParcel.StreetName
,tblDataParcel.StreetNum
,tblDataParcel.Location
,tblNumbersApplication.Number AS [tblNumbersApplication Number]
,tblNumbersApplication.Label AS [tblNumbersApplication Label]
,tblNumbersApplicationStatus.Number AS [tblNumbersApplicationStatus Number]
,tblNumbersApplicationStatus.Label AS [tblNumbersApplicationStatus Label]
,tblDataPermit.PermitCost
,tblDataFees.ApplicationNumber AS [tblDataFees ApplicationNumber]
,tblDataFees.Amount
,tblDataPermit.IssueDate
FROM
tblDataPermit
INNER JOIN tblDataParcel
ON tblDataPermit.ParcelID = tblDataParcel.ParcelID
INNER JOIN tblNumbersApplication
ON tblDataPermit.Type = tblNumbersApplication.Number
INNER JOIN tblNumbersApplicationStatus
ON tblDataPermit.Status = tblNumbersApplicationStatus.Number
INNER JOIN tblDataFees
ON tblDataPermit.ApplicationNumber = tblDataFees.ApplicationNumber
WHERE
tblDataPermit.Type = @Type
AND tblDataPermit.Status IN (@Status)
AND tblDataPermit.IssueDate >= @IssueDate
AND (tblDataPermit.IssueDate <= @IssueDate2
OR tblDataPermit.IssueDate IS NULL)>>
January 5, 2017 at 7:46 am
You need to use a LEFT JOIN instead. Taken from FROM (Transact-SQL):
msdn
LEFT [ OUTER ]Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join.
Sample usage:
SELECT C.CustomerName,
CN.PhoneNumber AS MobileNumber
FROM Customer C
LEFT JOIN ContactNumber CN ON C.CustomerID = CN.CustomerID
AND CN.NumberType = 'Mobile'
WHERE C.CustomerName = 'John Smith';
You should be able to use this information to do this yourself 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 5, 2017 at 8:23 am
Don't be scared to use aliases and add some white space to make your queries more legible. As posted your query resembles a wall of text and would be extremely painful to work with. Utilizing aliases and white space makes this so much easier to read. For your actual issue you just need to use a left join instead of an inner join as already pointed out.
SELECT
dp.ApplicationNumber
, dp.PermitNumber
, dp.Type
, dp.Status
, dp.ApplicationType
, dpa.StreetName
, dpa.StreetNum
, dpa.Location
, na.Number AS [tblNumbersApplication Number]
, na.Label AS [tblNumbersApplication Label]
, nas.Number AS [tblNumbersApplicationStatus Number]
, nas.Label AS [tblNumbersApplicationStatus Label]
, dp.PermitCost
, df.ApplicationNumber AS [tblDataFees ApplicationNumber]
, df.Amount
, dp.IssueDate
FROM tblDataPermit dp
INNER JOIN tblDataParcel dpa ON dp.ParcelID = dpa.ParcelID
INNER JOIN tblNumbersApplication na ON dp.Type = na.Number
INNER JOIN tblNumbersApplicationStatus nas ON dp.Status = nas.Number
LEFT JOIN tblDataFees df ON dp.ApplicationNumber = df.ApplicationNumber
WHERE dp.Type = @Type
AND dp.Status IN (@Status) --Why IN? If this is a delimited list it will never work. Might as well be =
AND dp.IssueDate >= @IssueDate
AND
(
dp.IssueDate <= @IssueDate2
OR
dp.IssueDate IS NULL
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2017 at 8:30 am
As Sean indicated, formatting your code helps make it legible to humans. Computers for the most part don't care about white space (unless you are working with Python, where it is critical) but is really help us and you when working with code.
January 5, 2017 at 9:38 am
I've been copying the code as created by SQL Report Builder. Thank you for the advice on formatting. I'm learning. 🙂
January 5, 2017 at 9:45 am
One more thing:
The report parameters allow me to choose multiple applications types and multiple status types.
If I select one of each it runs ok. If I select one application type and multiple status types it runs ok.
But, if I select multiple application types I get the error message: Query execution failed. Incorrect syntax near ','.
Any thoughts?
January 5, 2017 at 9:46 am
bzoom100 (1/5/2017)
I've been copying the code as created by SQL Report Builder. Thank you for the advice on formatting. I'm learning. 🙂
Part of the problem was that you didn't use the IFCode Shortcuts that are on the left when you posted your question, so it was treated as plain text instead of pre-formatted text, and it collapsed multiple spaces into one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 5, 2017 at 9:59 am
bzoom100 (1/5/2017)
One more thing:The report parameters allow me to choose multiple applications types and multiple status types.
If I select one of each it runs ok. If I select one application type and multiple status types it runs ok.
But, if I select multiple application types I get the error message: Query execution failed. Incorrect syntax near ','.
Any thoughts?
This sounds like you haven't set up your sp/sql to accept multiple value parameter.
In SSRS when passing a single value for a parameter, then the value is nice and simple; 'MyValue'. When passing multiple values, SSRS passes the variables in a delimited String. For example 'MyValue, MyValue2, MyValue3'. If you're therefore passing integers, everything goes wrong ('1, 2, 3' is not an Integer value).
You'll need to use a Split function if you want to use MultiValue Parameters with SSRS. Have a look at the SQL 8K “CSV Splitter” Function[/url] on SQL Server Central for a very efficient solution.
Your WHERE clause would then contain the following instead:
dp.Type IN (SELECT s.item FROM dbo.DelimitedSplit8k(@Type,',') s)
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 5, 2017 at 9:59 am
Ooops, never mind last one, i figured it out!!
One new issue has popped up however, it is not bringing in data where the IssueDate is NULL
January 5, 2017 at 10:11 am
bzoom100 (1/5/2017)
Ooops, never mind last one, i figured it out!!One new issue has popped up however, it is not bringing in data where the IssueDate is NULL
That is because of your where predicate.
AND dp.IssueDate >= @IssueDate
If dp.IssueDate is null it will never be returned. You would need to the same type of check with "OR dp.IssueDate IS NULL" here that you did on the other date check.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 5, 2017 at 10:18 am
You are all geniuses, thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply