February 20, 2017 at 9:21 am
This needs to be a SQL view for C# to consume. I am sure there is a better way to write this, but I am not aware of how to do it.
SELECT S.Id,
T.Id,
CASE
WHEN SAC.Contacts_Id IS NULL THEN
TAC.Contacts_Id
ELSE
SAC.Contacts_Id
END as AssignedContacts_Id,
CASE
WHEN SAC.OwnerType IS NULL THEN
TAC.OwnerType
ELSE
SAC.OwnerType
END as OwnerType,
CASE
WHEN SAC.ContactsId IS NULL THEN
TAC.ContactsId
ELSE
SAC.ContactsId
END as ContactsId,
CASE
WHEN SAC.Description IS NULL THEN
TAC.Description
ELSE
SAC.Description
END as Description
FROM [dbo].[ChildTable1] S WITH (NOLOCK)
LEFT JOIN [dbo].[Table1] T WITH (NOLOCK) ON S.ID = T.ID
LEFT JOIN [dbo].[Contacts] SAC WITH (NOLOCK) ON SAC.Owner_Id = S.ID AND SAC.OwnerType = 1
LEFT JOIN [dbo].[Contacts] TAC WITH (NOLOCK) ON TAC.Owner_Id = T.ID AND TAC.OwnerType = 0
This might not be perfect SQL since I changed table names for this post. It is just meant to convey that we are trying to say the following:
If Child table has contacts we want to display those contacts. If Child table does not have a contact associated, use Table1's contact list instead.
February 20, 2017 at 9:30 am
Is there any reason why you're using the NOLOCK hint? Also, perhaps ISNULL (Transact-SQL) or COALESCE (Transact-SQL) would be better choices than a CASE Statement for you logic and readability.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 12:13 pm
SELECT S.Id,
T.Id,
COALESCE(SAC.Contacts_Id, TAC.Contacts_Id) AS AssignedContacts_Id,
COALESCE(SAC.OwnerType, SAC.OwnerType) AS OwnerType,
COALESCE(SAC.ContactsId, TAC.ContactsId) AS ContactsId,
COALESCE(SAC.Description, TAC.Description) AS Description
FROM [dbo].[ChildTable1] S WITH (NOLOCK)
LEFT JOIN [dbo].[Table1] T WITH (NOLOCK) ON S.ID = T.ID
LEFT JOIN [dbo].[Contacts] SAC WITH (NOLOCK) ON SAC.Owner_Id = S.ID AND SAC.OwnerType = 1
LEFT JOIN [dbo].[Contacts] TAC WITH (NOLOCK) ON SAC.Owner_Id IS NULL AND TAC.Owner_Id = T.ID AND TAC.OwnerType = 0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 20, 2017 at 12:49 pm
Thanks for the ideas. Both isnull and Coelesce work. I chose isnull, but really they both were fine. thanks for the help
February 20, 2017 at 12:53 pm
Btw, I also added a clause to the TAC join:
LEFT JOIN [dbo].[Contacts] TAC WITH (NOLOCK) ON SAC.Owner_Id IS NULL AND ...
You might gain some slight efficiency from that, and, at any rate, it makes it explicitly clear that you only want a match on either SAC or TAC, not both.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 20, 2017 at 1:00 pm
JKSQL - Monday, February 20, 2017 12:49 PMThanks for the ideas. Both isnull and Coelesce work. I chose isnull, but really they both were fine. thanks for the help
You did not respond to Thom's question about the use of NOLOCK. Are you aware of the potential problems with using it (reading data twice, not reading data at all, reading 'ghost' data that never even made it into the database)? You need to make sure that you understand and accept the risks.
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
February 20, 2017 at 1:00 pm
Why not read the contacts table in one go?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
S.Id,
s.Id, -- S.ID = T.ID
x.AssignedContacts_Id,
x.OwnerType,
x.[Description]
FROM [dbo].[ChildTable1] S
LEFT JOIN [dbo].[Table1] T
ON S.ID = T.ID
OUTER APPLY (
SELECT TOP 1
AssignedContacts_Id = Contacts_Id,
OwnerType,
[Description]
FROM [dbo].[Contacts] TAC
WHERE TAC.Owner_Id = s.ID
ORDER BY TAC.OwnerType DESC
) x
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2017 at 1:06 pm
The Nolocks is how they want views written for reports so there are less page locks. I guess they are not concerned about ghost reads. We have not had adverse issues with this reporting standard.
<a title="Go to ChrisM@homes profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl07_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisM@home" style="text-decoration: none; color: rgb(0, 51, 102); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@home - would that work? i would think that would only give child table records?
February 20, 2017 at 1:12 pm
JKSQL - Monday, February 20, 2017 1:06 PMThe Nolocks is how they want views written for reports so there are less page locks. I guess they are not concerned about ghost reads. We have not had adverse issues with this reporting standard.
ChrisM@homes profile" id="ctl00_ctlContentPlaceHolder_ctl00_ctl00_ctlTopic_ctl00_ctlPanelBar_ctlTopicsRepeater_ctl07_hypUsername" class="i-type-bold" href="https://www.sqlservercentral.com/Forums/Users/ChrisM@home" style="text-decoration: none; color: rgb(0, 51, 102); cursor: pointer; font-weight: 600; font-family: Arial, Helvetica, sans-serif; font-size: 12.8px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;">ChrisM@home - would that work? i would think that would only give child table records?
Child tables are more or less left joined (OUTER APPLY).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 20, 2017 at 3:06 pm
ChrisM@home - Monday, February 20, 2017 1:00 PMWhy not read the contacts table in one go?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT
S.Id,
s.Id, -- S.ID = T.ID
x.AssignedContacts_Id,
x.OwnerType,
x.[Description]
FROM [dbo].[ChildTable1] S
LEFT JOIN [dbo].[Table1] T
ON S.ID = T.ID
OUTER APPLY (
SELECT TOP 1
AssignedContacts_Id = Contacts_Id,
OwnerType,
[Description]
FROM [dbo].[Contacts] TAC
WHERE TAC.Owner_Id = s.ID
ORDER BY TAC.OwnerType DESC
) x
I wish I had more time to dig into this, but I'm not sure that gives the correct output in all cases (such as OwnerType of -10 maybe). Even if it does, I am almost positive that there is a way to solve this need with a single join and no TOP/ORDER BY overhead using a creative JOIN clause and CASE statements.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 1:37 am
JKSQL - Monday, February 20, 2017 1:06 PMThe Nolocks is how they want views written for reports so there are less page locks. I guess they are not concerned about ghost reads. We have not had adverse issues with this reporting standard.
Are they aware that it's not just dirty reads they'll get with nolock? That rows can be read twice or not seen at all?
http://source.entelect.co.za/the-effects-of-the-nolock-hint
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2017 at 6:01 am
JKSQL - Monday, February 20, 2017 1:06 PMThe Nolocks is how they want views written for reports so there are less page locks. I guess they are not concerned about ghost reads. We have not had adverse issues with this reporting standard.
If I'm correct, the number of locks would be the same. The only difference is that readers won't be blocked by writers. The NOLOCK hint is misleading as it's not intended to avoid creating locks but only to ignore the ones that were set.
February 21, 2017 at 6:08 am
Luis Cazares - Tuesday, February 21, 2017 6:01 AMIf I'm correct, the number of locks would be the same. The only difference is that readers won't be blocked by writers. The NOLOCK hint is misleading as it's not intended to avoid creating locks but only to ignore the ones that were set.
No.
If a query is running in read uncommitted, it takes no shared locks, hence it doesn't get blocked by existing update or exclusive locks.
The only lock it'll take is a schema-stability lock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2017 at 6:12 am
This might work for a single read on the Contacts table, but I can't guarantee any improvement or even correctness.
SELECT S.Id,
T.Id,
SAC.Contacts_Id AS AssignedContacts_Id,
SAC.OwnerType,
SAC.ContactsId,
SAC.Description,
FROM [dbo].[ChildTable1] S
LEFT JOIN [dbo].[Table1] T ON S.ID = T.ID
LEFT JOIN [dbo].[Contacts] SAC ON (SAC.Owner_Id = S.ID AND SAC.OwnerType = 1
OR SAC.Owner_Id = T.ID AND SAC.OwnerType = 0);
February 21, 2017 at 9:49 am
Luis Cazares - Tuesday, February 21, 2017 6:12 AMThis might work for a single read on the Contacts table, but I can't guarantee any improvement or even correctness.
SELECT S.Id,
T.Id,
SAC.Contacts_Id AS AssignedContacts_Id,
SAC.OwnerType,
SAC.ContactsId,
SAC.Description,
FROM [dbo].[ChildTable1] S
LEFT JOIN [dbo].[Table1] T ON S.ID = T.ID
LEFT JOIN [dbo].[Contacts] SAC ON (SAC.Owner_Id = S.ID AND SAC.OwnerType = 1
OR SAC.Owner_Id = T.ID AND SAC.OwnerType = 0);
You should just need a CASE statement to "prioritize" the output values as the initial query does.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply