Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

View not displaying all results Expand / Collapse
Posted Thursday, August 7, 2014 9:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 27, 2016 12:20 AM
Points: 25, Visits: 80
I have created a view thats pulling data from two different tables to combine them into one report.

table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.

When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.

Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?


SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
Post #1601007
Posted Thursday, August 7, 2014 9:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:13 PM
Points: 618, Visits: 3,469
Just quickly off the top of my head, and without looking in detail at the query, have you looked at left and right joins instead of the inner join?

Give me everything from table 1 regardless of its existence in table 2 and then give me details, where they exist, from table 2? This would be a left outer join.

Post #1601008
Posted Friday, August 8, 2014 12:04 AM



Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 7,078, Visits: 17,179
Agree with previous comment. Try changing INNER JOIN to LEFT JOIN and see whether that helps you.

Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Never attribute to malice that which is adequately explained by stupidity
Post #1601029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse