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: Today @ 1:33 AM
Points: 27, Visits: 87
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: Today @ 11:58 AM
Points: 620, Visits: 3,583
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: Today @ 11:28 AM
Points: 7,958, Visits: 19,059
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.

If your posting includes some T-SQL code, please surround the code with IFCode formatting tags. It helps readability a lot.
Post #1601029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse