Stuck on querying 3 tables where I'm looking for non-matches

Question

Stuck on querying 3 tables where I'm looking for non-matches

Melanie Peterson

SSCrazy

Points: 2387
More actions
April 11, 2013 at 2:41 pm

#276316

I have three tables: LitHold, LitHoldDetails and EmailTemplate. The definitions are as follows.
CREATE TABLE [dbo].[LitHold](
[LitholdID] [int] IDENTITY(1,1) NOT NULL,
[LitHoldStatusID] [tinyint] NOT NULL,
[EmailReminderID] [tinyint] NULL,
[ApprovedDate] [datetime] NULL,
[TerminatedDate] [datetime] NULL,
CONSTRAINT [PK_Lithold] PRIMARY KEY CLUSTERED
(
[LitholdID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[LitHoldDetails](
[LitHoldDetailsID] [int] IDENTITY(1,1) NOT NULL,
[LitholdID] [int] NOT NULL,
[VersionID] [int] NOT NULL,
[Description] [varchar](300) NULL,
[ResAttorneyID] [varchar](10) NOT NULL,
[Comments] [varchar](1000) NULL,
[HoldStartDate] [datetime] NULL,
[HoldEndDate] [datetime] NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedByLogin] [varchar](10) NULL,
CONSTRAINT [PK_LitholdDetails] PRIMARY KEY CLUSTERED
(
[LitHoldDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[EmailTemplate](
[TemplateID] [int] IDENTITY(1,1) NOT NULL,
[LitHoldDetailsID] [int] NOT NULL,
[From] [varchar](50) NULL,
[To] [varchar](2000) NULL,
[CC] [varchar](500) NULL,
[BCC] [varchar](500) NULL,
[Subject] [nvarchar](200) NULL,
[MessageBody] [nvarchar](max) NULL,
[SendDate] [datetime] NULL,
[IsDefault] [bit] NOT NULL,
CONSTRAINT [PK_EmailTemplate] PRIMARY KEY CLUSTERED
(
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For each LitHold, there can be multiple LitHoldDetails. For each LitHoldDetail, there should be one EmailTemplate. I recently found that some LitHoldDetails do NOT have EmailTemplates. We're still working in development on this project, so this isn't a big deal. However, we want to get the EmailTemplate data into the database. The situation is that for each LitHold, there is at least one LitHoldDetail that has an EmailTemplate. I would like to duplicate this EmailTemplate data for all the LitHoldDetails that a) have the same LitHoldID and b) do not have an EmailTemplate. One of the approaches I've tried is:
insert into EmailTemplate
(LitHoldDetailsID, [From], [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault)
(select (select LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID), [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from
EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID)
but this gets me the error message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." How can I accomplish this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

Your query is failing because there are more than 1 row in LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID. Since this is dev data I assume you don't really care which row it gets as long as it gets something.

This will ensure that only 1 row is returned by your subquery. There is no order by so there is no guarantee which row will be returned though.

select (select top 1 LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID), [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from

EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 2

Ah! I knew why I was getting the error message, but using TOP never occurred to me. However, I think there's still something wrong. With the new query:

insert into EmailTemplate
(LitHoldDetailsID, [From], [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault)
(select (select top 1 LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID) AS LitHoldDetailsID, [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from
EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID)

I'm getting some duplicate LitHoldDetailsID's (but the other fields are distinct values), and I'm getting some NULL LitHoldDetailsID's. I think my approach may be flawed.... 😉

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Melanie Peterson (4/11/2013)
Ah! I knew why I was getting the error message, but using TOP never occurred to me. However, I think there's still something wrong. With the new query:
insert into EmailTemplate
(LitHoldDetailsID, [From], [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault)
(select (select top 1 LitHoldDetailsID from LitHoldDetails where LitholdID = d.LitholdID and LitHoldDetailsID <> e.LitHoldDetailsID) AS LitHoldDetailsID, [To], CC, BCC, Subject, MessageBody, SendDate, IsDefault from
EmailTemplate e inner join LitHoldDetails d on e.LitHoldDetailsID = d.LitHoldDetailsID)
I'm getting some duplicate LitHoldDetailsID's (but the other fields are distinct values), and I'm getting some NULL LitHoldDetailsID's. I think my approach may be flawed.... 😉

Of course you are going to end up with some duplicate ID's. You could also end up with NULL because of the way your query is put together.

I can't really provide much assistance since I don't know any of your structures. 🙂

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Shouldn't templateID be a column in LitHoldDetails? I think you've got the relationship the wrong way round.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 5

LitHold and LitHoldDetails are connected via the LitHoldID column, and LitHoldDetails and EmailTemplate are connected via the LitHoldDetailsID column. Apart from that and the table structures, what else do you need to know? I'm happy to provide any info.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Melanie Peterson (4/12/2013)
LitHold and LitHoldDetails are connected via the LitHoldID column, and LitHoldDetails and EmailTemplate are connected via the LitHoldDetailsID column. Apart from that and the table structures, what else do you need to know? I'm happy to provide any info.

Yes - but you're holding a key for LitHoldDetails in table EmailTemplate. This means the same email template is repeated squillions of times, once for each LitHoldDetails which uses the template. If you held the templateid in LitHoldDetails instead, you'd have far fewer rows in EmailTemplate.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Melanie Peterson SSCrazy Points: 2387 More actions · Answer 7

No, LitHold and LitHoldDetails are in a one-to-many relationship, but LitHoldDetails and EmailTemplate are in a one-to-one relationship. For every LitHoldDetails, there is one (or should be one) EmailTemplate. My problem is that we've lost data due to a bug during development and I want to put *some kind of data* back. My solution is to give each LitHoldDetails the same EmailTemplate data, i.e., the EmailTemplate data that the one LitHoldDetails with which they have a LitHold in common has. An example is probably better:

LitHold LitHoldDetails EmailTemplate

1 1 we have data

1 2 no data

2 3 we have data

3 4 we have data

3 5 no data

3 6 no data

I want to give LitHoldDetails 2 the EmailTemplate data from LitHoldDetails 1, LitHoldDetails 5 and 6 the EmailTemplate data from LitHoldDetails 4. I hope this helps.