September 5, 2014 at 12:27 am
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
dbo.vcltAttrib4 ON dbo.cdbClient.objInstID = dbo.vcltAttrib4.ainObjectInstID LEFT OUTER JOIN
dbo.vcltAttrib6 ON dbo.cdbClient.objInstID = dbo.vcltAttrib6.ainObjectInstID INNER JOIN
dbo.vcltAttrib422 ON dbo.cdbClient.objInstID = dbo.vcltAttrib422.ainObjectInstID INNER JOIN
dbo.cdbAttribInst ON dbo.vcltAttrib4.ainObjectInstID = dbo.cdbAttribInst.ainObjectInstID
WHERE (CONVERT(VARCHAR(8), dbo.vcltAttrib422.ainDVal, 112) BETWEEN CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 12, GETDATE()), 0), 112) AND
CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0), 112)) AND (dbo.cdbAttribInst.ainAttribID = 21)
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'
September 5, 2014 at 12:44 am
cstrati (9/5/2014)
I have a view I've created which displays client sortname, partner and date added which displays 7 results.When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
dbo.vcltAttrib4 ON dbo.cdbClient.objInstID = dbo.vcltAttrib4.ainObjectInstID LEFT OUTER JOIN
dbo.vcltAttrib6 ON dbo.cdbClient.objInstID = dbo.vcltAttrib6.ainObjectInstID INNER JOIN
dbo.vcltAttrib422 ON dbo.cdbClient.objInstID = dbo.vcltAttrib422.ainObjectInstID INNER JOIN
dbo.cdbAttribInst ON dbo.vcltAttrib4.ainObjectInstID = dbo.cdbAttribInst.ainObjectInstID
WHERE (CONVERT(VARCHAR(8), dbo.vcltAttrib422.ainDVal, 112) BETWEEN CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 12, GETDATE()), 0), 112) AND
CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0), 112)) AND (dbo.cdbAttribInst.ainAttribID = 21)
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'
Try this:
SELECT dbo.cdbClient.cltSortName AS ClientName ,
dbo.vcltAttrib4.ainTVal AS ClientPartner ,
dbo.vcltAttrib422.ainDVal AS [Date Added] ,
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient
LEFT OUTER JOIN dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID
LEFT OUTER JOIN dbo.vcltAttrib4 ON dbo.cdbClient.objInstID = dbo.vcltAttrib4.ainObjectInstID
LEFT OUTER JOIN dbo.vcltAttrib6 ON dbo.cdbClient.objInstID = dbo.vcltAttrib6.ainObjectInstID
INNER JOIN dbo.vcltAttrib422 ON dbo.cdbClient.objInstID = dbo.vcltAttrib422.ainObjectInstID
LEFT JOIN dbo.cdbAttribInst ON dbo.vcltAttrib4.ainObjectInstID = dbo.cdbAttribInst.ainObjectInstID
WHERE ( CONVERT(VARCHAR(8), dbo.vcltAttrib422.ainDVal, 112) BETWEEN CONVERT(VARCHAR(8), DATEADD(wk,
DATEDIFF(wk, 12,
GETDATE()), 0), 112)
AND
CONVERT(VARCHAR(8), DATEADD(wk,
DATEDIFF(wk, 0,
GETDATE()), 0), 112) )
AND ( dbo.cdbAttribInst.ainAttribID = 21
OR dbo.cdbAttribInst.ainAttribID IS NULL
)
I changed the join to your new table to a LEFT JOIN and added an OR IS NULL in the WHERE clause.
Note also that if you were to start using table aliases, your code would become much more readable 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2014 at 12:46 am
Also, if lots of data is involved, that WHERE clause is going to tax the database engine because of all those function calls. We may be able to help optimise this query for you, if performance is a problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2014 at 12:51 am
Hi Phil
Thanks for your reply.
Unfortunately I've ran your script and I'm getting the same 4 results.
It doesn't appear that the columns that are emtpy display when adding 'Null' to the filter however there's no data in them.
September 5, 2014 at 1:01 am
cstrati (9/5/2014)
Hi PhilThanks for your reply.
Unfortunately I've ran your script and I'm getting the same 4 results.
It doesn't appear that the columns that are emtpy display when adding 'Null' to the filter however there's no data in them.
OK, try changing the AND bit as follows:
and (
dbo.cdbAttribInst.ainAttribID = 21
or coalesce(rtrim(dbo.cdbAttribInst.ainAttribID),'') = ''
)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2014 at 1:21 am
I have changed the and statement however I still get 4 results unfortunately.
I've attached the query results with the Industry and without the Industry table.
Instead of showing the industry table as empty for those without an industry it removes the row completely and only displays the results that have an industry.
September 5, 2014 at 1:31 am
If you remove the AND clause completely, are the required rows displayed?
Please do a SELECT from dbo.cdbAttribInst showing the value of ainAttribID for the rows you want to appear and include that as a screen shot too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 5, 2014 at 1:44 am
Phil Parkin (9/5/2014)
cstrati (9/5/2014)
Hi PhilThanks for your reply.
Unfortunately I've ran your script and I'm getting the same 4 results.
It doesn't appear that the columns that are emtpy display when adding 'Null' to the filter however there's no data in them.
OK, try changing the AND bit as follows:
and (
dbo.cdbAttribInst.ainAttribID = 21
or coalesce(rtrim(dbo.cdbAttribInst.ainAttribID),'') = ''
)
Quick thought, the left outer joins are effectively turned into inner joins. Can you provide DDL/Sample Data so we can quickly solve this problem? The code below stands a better chance but might need adjusting because of the where clause.
😎
SELECT
CLI.cltSortName AS ClientName
,ATR4.ainTVal AS ClientPartner
,ATR422.ainDVal AS [Date Added]
,ATRINS.ainTVal AS Inudstry
FROM dbo.cdbClient CLI
LEFT OUTER JOIN dbo.cdbObject OBJ
ON CLI.cltCategoryID = OBJ.objID
LEFT OUTER JOIN dbo.vcltAttrib4 ATR4
ON CLI.objInstID = ATR4.ainObjectInstID
LEFT OUTER JOIN dbo.vcltAttrib6 ATR6
ON CLI.objInstID = ATR6.ainObjectInstID
LEFT OUTER JOIN dbo.vcltAttrib422 ATR422
ON CLI.objInstID = ATR422.ainObjectInstID
LEFT OUTER JOIN dbo.cdbAttribInst ATRINS
ON ATR4.ainObjectInstID = ATRINS.ainObjectInstID
WHERE (CONVERT(VARCHAR(8), ATR422.ainDVal, 112) BETWEEN CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 12, GETDATE()), 0), 112) AND
CONVERT(VARCHAR(8), DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0), 112)) AND (ATRINS.ainAttribID = 21)
September 5, 2014 at 2:01 am
Try this:
SELECT
c.cltSortName AS ClientName,
a4.ainTVal AS ClientPartner,
a422.ainDVal AS [Date Added],
ai.ainTVal AS Inudstry
FROM dbo.cdbClient c
LEFT OUTER JOIN dbo.cdbObject o
ON c.cltCategoryID = o.objID
LEFT OUTER JOIN dbo.vcltAttrib4 a4
LEFT OUTER JOIN dbo.vcltAttrib6 a6
ON c.objInstID = a6.ainObjectInstID
INNER JOIN dbo.vcltAttrib422 a422
ON c.objInstID = a422.ainObjectInstID
inner JOIN dbo.cdbAttribInst ai -- inner join grandchild to child table first
ON a4.ainObjectInstID = ai.ainObjectInstID
AND ai.ainAttribID = 21
ON c.objInstID = a4.ainObjectInstID -- left join child to parent second
WHERE CAST(a422.ainDVal AS DATE)
BETWEEN CAST(DATEADD(wk, DATEDIFF(wk, 12, GETDATE()), 0) AS DATE)
AND CAST(DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AS DATE)
If it works, I'll post up an explanation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply