Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

View not displaying all results Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 9:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:24 PM
Points: 19, Visits: 51
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?





Script:

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
ORDER BY ClientID
Post #1601007
Posted Thursday, August 7, 2014 9:34 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:16 PM
Points: 597, Visits: 2,971
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 5,151, Visits: 11,993
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.
Post #1601029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse