Complex SQL Query

Well, at least I think it's complex! It might be easy for you!

I have the following 3 tables:

ContentPages (Primary Key is PageID) stores information about pages, such as where the file is on the server, who created the page etc. ContentProtocols (PK is ProtocolID) stores the names of "protocols". In my content management system, a protocol is made up of a sequence of pages. So "Ben's Protocol" could be a sequence of "BensPage1.html", "BensPage2.html" and "BensPage3.aspx". The order of the pages is important. ContentProtocolsPages. This table stores pageIDs next to protocolIDs so that the pages are "in" protocols. It also stores the order of the pages.

ContentProtocolsPages has three columns:

PageID (foreign key from ContentPages table) ProtocolID (FK from ContentProtocols table) Position. This is an integer column that stores the position of a page within a protocol, so the first page in a protocol has position 1. The second has position 2. The third has position 3 and so on.

The PageID and Position columns combine to make the primary key in the ContentProtocolPages table, because obviously you can't have more than one page occupying a given position in a protocol. e.g. You couldn't have "BenPage1.html" occupying "Ben's Protocol" position 2 and "BenPage2.html" occupying the same position in the same protocol.

The problem occurs when deleting a page.

The logic is:
Before a page can be deleted from the ContentPages table, it has to be taken out of any protocols, so all record with that page in ContentProtocolsPages have to be deleted. This means that any pages in the same protocol AFTER the deleted page need to have their position set to one less (Position=Position-1), to account for the page being removed. So, if you delete the page with position 3 in a six page protocol, the SQL statement has to set the pages with positions 4, 5 and 6 to be 3, 4 and 5 respecively.

You can't do that BEFORE deleting the record of the page in ContentProtocolsPages because that violates the primary key constraint, but you can't do it AFTER either, because then you have no idea what Position that page used to occupy.

Here's what I have so far, but I'm getting an error because of the composite primary key.

UPDATE ContentProtocolsPages
SET Position=Position-1
WHERE Position > (SELECT Position FROM ContentProtocolsPages WHERE PageID=@.PageID)
AND ProtocolID = (SELECT ProtocolID FROM ContentProtocolsPages WHERE PageID=@.PageID)

DELETE FROM ContentProtocolsPages WHERE PageID=@.PageID

DELETE FROM ContentPages WHERE PageID=@.PageID

Any Ideas? Many thanks to anybody who can help out with this problem

Yes absolutely. How silly of me. The ProtocolID and PositionID columns combine to make the Primary Key. Thanks very much for pointing that out. Any ideas regarding the problem, now correctly stated?
|||Ok. I thougt I had jumped the Conclusion. So I deleted the post. If you are using SQL SERVER 2005 try using the OUTPUT CLAUSE of delete statement.|||Thanks very much, but I'm afraid I've never heard of that. Can you recommend an article on how I should use it to achieve my purpose?
|||

you can also try an AFTER TRIGGER. Here is the sample code

ALTER TRIGGER MyDelTRG

ON [ContentProtocolsPages]

AFTER DELETE

AS

DECLARE @.MyDeleteCursor CURSOR

DECLARE @.PageID INT,@.ProtocolID INT,@.Position INT

-- Curosor Definition

SET @.MyDeleteCursor = CURSOR FAST_FORWARD

FOR

SELECT PageID,ProtocolID,Position FROM DELETED

OPEN @.MyDeleteCursor

FETCH NEXT FROM @.MyDeleteCursor

INTO @.PageID,@.ProtocolID,@.Position

--LOOP all records from the inserted table

WHILE @.@.FETCH_STATUS = 0

BEGIN

UPDATE ContentProtocolsPages SET Position=Position-1

WHERE Position>=@.Position

FETCH NEXT FROM @.MyDeleteCursor

INTO @.PageID,@.ProtocolID,@.Position

CLOSE @.MyDeleteCursor

DEALLOCATE @.MyDeleteCursor

END

|||That looks great! I don't understand it, but I'll try and learn it as I go along. On first inspection it looks like either of your suggestions could work. Do you recommend either in particular or doesn't it matter? Anyway, thanks for all your help, I'll see if I can put it into action.
Ben
|||Let me know if the code worked for you

Complex SELECT pulling data from three tables

OK, everyone. I have hit a wall while trying to accomplish the
following goal. I have a table (tblDocInfo) that stores information
about documents. Among the information being stored, I have three
fields that store information about attorneys (ReviewingAtty,
DraftingAtty, ContactAtty) who are usually three different people. The
information I am storing in those three fields is of type INT. Those
numbers correspond to the Primary Key in another table (tblAtty) where
the attorney data is stored. For example:
tblDocInfo {
ID int
ReviewingAtty int
DraftingAtty int
ContactAtty int
docDate date
Comments varchar
}
I am trying to execute a query where I can pull the attorney names
along with some other document information, but I am not getting the
results that I want as the three attorneys are normally different and
only one name is coming accross.
For example, if in "tblDocInfo" I have this data (see format above):
23, 2, 4, 6, 23/09/05, Will
24, 1, 5, 3, 11/05/05, Contract
30, 2, 2, 6, 22/09/05, Letter
41, 4, 4, 5, 23/09/05, Warning
and in "tblAtty" I have this data:
1, John Doe
2, Jane Doe
3, Peter Somebody
4, Joe Blow
5, John Smith
6, Michael Cooper
I would like my query to display this:
23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
Thank you.
MarcosSomething like this...
select a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
from tblDocInfo a
join tblAtty a1
on a1.id = a. ReviewingAtty
join tblAtty a2
on a2.id = a. DraftingAtty
join tblAtty a3
on a3.id = a. ContactAtty
group by a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
MJKulangara
http://sqladventures.blogspot.com|||I only see two tables here, and I think there is certainly a better and more
relational design waiting for you to discover it. But for now:
USE tempdb;
GO
CREATE TABLE dbo.Attorneys
(
AttorneyID INT PRIMARY KEY,
FullName VARCHAR(64)
);
GO
CREATE TABLE dbo.DocumentInfo
(
DocumentID INT PRIMARY KEY,
ReviewingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
DraftingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
ContactAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
docDate SMALLDATETIME,
Comments VARCHAR(255)
);
GO
SET NOCOUNT ON;
INSERT dbo.Attorneys SELECT 1, 'John Doe';
INSERT dbo.Attorneys SELECT 2, 'Jane Doe';
INSERT dbo.Attorneys SELECT 3, 'Peter Somebody';
INSERT dbo.Attorneys SELECT 4, 'Joe Blow';
INSERT dbo.Attorneys SELECT 5, 'John Smith';
INSERT dbo.Attorneys SELECT 6, 'Michael Cooper';
INSERT dbo.DocumentInfo SELECT 23, 2, 4, 6, '20050923', 'Will';
INSERT dbo.DocumentInfo SELECT 24, 1, 5, 3, '20050511', 'Contract';
INSERT dbo.DocumentInfo SELECT 30, 2, 2, 6, '20050922', 'Letter';
INSERT dbo.DocumentInfo SELECT 41, 4, 4, 5, '20050923', 'Warning';
SELECT d.DocumentID, a1.FullName, a2.FullName, a3.FullName, d.docDate,
d.Comments
FROM dbo.documentInfo d
LEFT OUTER JOIN dbo.Attorneys a1
ON d.ReviewingAttorney = a1.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a2
ON d.DraftingAttorney = a2.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a3
ON d.ContactAttorney = a3.AttorneyID
GO
DROP TABLE dbo.DocumentInfo;
DROP TABLE dbo.Attorneys;
GO
"bodhipooh" <marcos.velez@.ipm.com> wrote in message
news:1139244065.032852.306060@.o13g2000cwo.googlegroups.com...
> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>|||Thank you all.
First of all, I got the problem solved. I was trying to do the JOIN
operation, but had a mistake in the order (syntax will always get
you!!) Your help is greatly appreciated. Second, I apologize for not
posting my entire table, or much more information. I realize that
information is often necessary to make it possible for others to be
able to help me, but I am working within some confidentiality
constraints and had to minimize the amount of information that was to
be posted. Still your replies have been VERY helpful.
Marcos|||This example may give you some idea on how to go about this:
http://milambda.blogspot.com/2005/0...s-as-array.html
For a better solution please post DDL and sample data.
ML
http://milambda.blogspot.com/|||SELECT t1.[ID], t2.[AttyName], t3.[AttyName], t4.[AttyName], t1.docdate,
t1.comments
FROM tblDocInfo t1
INNER JOIN tblAtty t2 ON t1.ReviewingAtty = t2.AttyID
INNER JOIN tblAtty t3 ON t1.DraftingAtty = t2.AttyID
INNER JOIN tblAtty t4 ON t1.ContactAtty = t2.AttyID
This would work as long as you don't allow any NULLs.
"bodhipooh" wrote:

> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>

Complex Query

Need to create a SELECT statement which is above my skills. There are two
tables involved, both are shown below. Table 1 stores messages to be
delivered to a user on a web page. Messages can be sent to a specific set o
f
users by setting BlockList to false and adding each user to Table 2 or to al
l
users by setting BlockList to true and adding no users to Table 2. Messages
can be active until acknowledged (EndDate is null) or to a specific EndDate.
The query must return all active messages for a specific user name (the only
parameter). A message should be returned if BlockList is false and the user
is listed, or BlockList is true and the user is not listed. In addition, if
there is an end date it must not be passed and if BlockList is false (meanin
g
the user list was established as a list of recipients) then the Recieved dat
e
on Table 2 must be null indicating the user has not yet received the message
.
I am open to table architecture changes which might allow the same
information to be conveyed in a simpler manner if those more experienced
think that is better than writing a query to extract this information.
Table 1: IntranetMessages
_Column Name_ _Type_ _Allow Null_
MessageID (PK) uniqueidentifier No
Text nvarchar(MAX) No
URL nvarchar(MAX) No
Tooltip nvarchar(MAX) No
BlockList bit No
IssueDate DateTime No
EndDate DateTime Yes
Table 2: IntranetMessageRecipients
_Column Name_ _Type_ _Allow Null_
RecID (PK) uniqueidentifier No
MessageID (FK) uniqueidentifier No
Recipient nvarchar(MAX) No
Received datetime Yesyou'll need a reference from the IntranetMessageRecipients to the
IntranetMessages to achieve this, aka foreign key.
is this possible?

> _Column Name_ _Type_ _Allow Null_
RecID int No
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
you could insert a default value for those messages that do not have a
recipient.

> Table 2: IntranetMessageRecipients

> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes|||Sorry, I marked the MessageID field in the IntranetMessageRecipients table a
s
a Foreign Key but didn't explicitely say that it corresponds to the MessageI
D
(PK) in the IntranetMessages table. That is how it is setup, however, if
that is what you mean.
"Gerard" wrote:

> you'll need a reference from the IntranetMessageRecipients to the
> IntranetMessages to achieve this, aka foreign key.
> is this possible?
>
> RecID int No
> you could insert a default value for those messages that do not have a
> recipient.
>
>
>|||SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipient
FROM IntranetMessages im
LEFT OUTER JOIN
IntranetMessageRecipients imr
ON im.MessageID = imr.MessageID
AND imr.Received IS NULL
WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
@.passedInUser
AND im.EndDate IS NULL
A couple of thoughts on your schema. I would be careful with the use of
uniqueidentifiers as your primary keys. For several reasons:
1) I hope that you have the primary key as a clustered index. It is a good
rule of thumb to always have a clustered index
2) Uniqueidentifiers are typically bad choices as clustered indexes since
they are not ever increasing. This causes your table to become fragmented
quickly. If you use uniqueidentifiers as your clustered primary key, you
should make sure that you are rebuilding the index regularly.
Beyond that, your schema is not normalized since you could have the same
username in your intranetMessage table many times. It would make this a bit
cleaner if you had a table with all users that is referenced by the
Recipients table.
But, I think that my query should do the trick for you.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:

> Need to create a SELECT statement which is above my skills. There are two
> tables involved, both are shown below. Table 1 stores messages to be
> delivered to a user on a web page. Messages can be sent to a specific set
of
> users by setting BlockList to false and adding each user to Table 2 or to
all
> users by setting BlockList to true and adding no users to Table 2. Messag
es
> can be active until acknowledged (EndDate is null) or to a specific EndDat
e.
> The query must return all active messages for a specific user name (the on
ly
> parameter). A message should be returned if BlockList is false and the us
er
> is listed, or BlockList is true and the user is not listed. In addition,
if
> there is an end date it must not be passed and if BlockList is false (mean
ing
> the user list was established as a list of recipients) then the Recieved d
ate
> on Table 2 must be null indicating the user has not yet received the messa
ge.
> I am open to table architecture changes which might allow the same
> information to be conveyed in a simpler manner if those more experienced
> think that is better than writing a query to extract this information.
> Table 1: IntranetMessages
> _Column Name_ _Type_ _Allow Null_
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes
>
>|||sorry misunderstanding on my part
maybe something like this will work (not tested)
select * from IntranetMessages
inner join IntranetMessageRecipients on
IntranetMessageRecipients.MessageID = IntranetMessages .MessageID
where
(BlockList = 0 and IntranetMessageRecipients.RecID = @.recp) or
(BlockList = 1 and IntranetMessageRecipients.RecID <> @.recp)
regards
Gerard|||Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers... I
don't know what a clustered index is, but that's for me to look up and your
point about usernames is well taken.
"Ryan Powers" wrote:
> SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipie
nt
> FROM IntranetMessages im
> LEFT OUTER JOIN
> IntranetMessageRecipients imr
> ON im.MessageID = imr.MessageID
> AND imr.Received IS NULL
> WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
> @.passedInUser
> AND im.EndDate IS NULL
> A couple of thoughts on your schema. I would be careful with the use of
> uniqueidentifiers as your primary keys. For several reasons:
> 1) I hope that you have the primary key as a clustered index. It is a goo
d
> rule of thumb to always have a clustered index
> 2) Uniqueidentifiers are typically bad choices as clustered indexes since
> they are not ever increasing. This causes your table to become fragmented
> quickly. If you use uniqueidentifiers as your clustered primary key, you
> should make sure that you are rebuilding the index regularly.
> Beyond that, your schema is not normalized since you could have the same
> username in your intranetMessage table many times. It would make this a b
it
> cleaner if you had a table with all users that is referenced by the
> Recipients table.
> But, I think that my query should do the trick for you.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Let me know if it works.
Also, if you need me to point you to a couple of sites about Clustered
indexes and tuning, let me know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers...
I
> don't know what a clustered index is, but that's for me to look up and you
r
> point about usernames is well taken.
> "Ryan Powers" wrote:
>|||If you don't mind a few references are always helpful. I am just getting my
feet wet in SQL Server so I have a lot to learn.
Your query got me 90% of the way there and once I read up on CASE and LEFT
OUTER JOIN statements to see what was going on I got it to do what I needed.
The end result follows:
SELECT im.MessageID, im.BlockList, imr.Recipient
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON im.MessageID =
imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList =
0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 END)
END = 1)
"Ryan Powers" wrote:
> Let me know if it works.
> Also, if you need me to point you to a couple of sites about Clustered
> indexes and tuning, let me know.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Sorry, I had replaced the parameters for testing. The real version is:
SELECT im.MessageID
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON
im.MessageID = imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN
im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1)
"MKing" wrote:
> If you don't mind a few references are always helpful. I am just getting
my
> feet wet in SQL Server so I have a lot to learn.
> Your query got me 90% of the way there and once I read up on CASE and LEFT
> OUTER JOIN statements to see what was going on I got it to do what I neede
d.
> The end result follows:
> SELECT im.MessageID, im.BlockList, imr.Recipient
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON im.MessageID
=
> imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList
=
> 0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 EN
D)
> END = 1)
> "Ryan Powers" wrote:
>|||Glad you got it to work.
I was trying to figure out why what I gave you did not work right and I see
two things. Because I think you can do something a bit cleaner than your
final solution.
1) It looks like you are saying match the recipient when blocklist is 0
instead of 1. Can you explain that (just for my curiousity)? Is it true tha
t
you want to look the recipient up when BlockList = 0? Is it also true that
the message will have no rows in the Recipient table when BlockList = 1?
2) I failed to include rows with end date later than today.
As far as references go, I prefer watching presentations/ webcasts. There
is a great one by Kimberly Tripp on index best practices that would give you
a great background on clustered indexes. It is focused on SQL 2005, but the
concepts transcend the releases. You can find it here.
http://www.microsoft.com/uk/technet...aspx?videoid=29
Beyond that, here are a couple good articles.
http://www.sql-server-performance.c..._structures.asp
http://www.sql-server-performance.c...red_indexes.asp
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Sorry, I had replaced the parameters for testing. The real version is:
> SELECT im.MessageID
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON
> im.MessageID = imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN
> im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1
)
> "MKing" wrote:
>

Comparing Two Tables Without a Cursor...HELP!

I have a project I am working on that has two tables. One is a reference
table, and the other is a table that stores incoming data from an outside
vendor. The customer wants us to write a "solution" that will compare the
incoming data table to the lookup table, and find records that don't match.
Any records in the incoming data table should have corresponding records in
the lookup table, and specific columns need to match. The "solution" will
create a list of records with anomolies.
I had planned to write a cursor to march through the lookup table and check
for records in the incoming data table, but the customer feels cursors are a
bad idea, and does not want us to use them.
Are there alternatives to cursors?
BV.Please post DDL along with
1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
incoming.col2 = lookup.col2...)
2) sample data [match and mismatch]
3) which data you're returning - in most cases, the desired result is
the incoming data w/o corresponding lookup values. however, you're
initial approach sounds like you want lookups that aren't in the
incoming data. whichever, a cursor is indeed unnecessary and probably bad
w/o the above all you'll get is guesses, like this
-- use NOT EXISTS
select * -- list columns in real code
from incoming i
where not exists (
select *
from lookup
where col1 = i.col1
and col2 = i.col2
)
BenignVanilla wrote:
> I have a project I am working on that has two tables. One is a reference
> table, and the other is a table that stores incoming data from an outside
> vendor. The customer wants us to write a "solution" that will compare the
> incoming data table to the lookup table, and find records that don't match
.
> Any records in the incoming data table should have corresponding records i
n
> the lookup table, and specific columns need to match. The "solution" will
> create a list of records with anomolies.
> I had planned to write a cursor to march through the lookup table and chec
k
> for records in the incoming data table, but the customer feels cursors are
a
> bad idea, and does not want us to use them.
> Are there alternatives to cursors?
> BV.
>|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Odbq1JkFGHA.516@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with
> 1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
> incoming.col2 = lookup.col2...)
> 2) sample data [match and mismatch]
> 3) which data you're returning - in most cases, the desired result is the
> incoming data w/o corresponding lookup values. however, you're initial
> approach sounds like you want lookups that aren't in the incoming data.
> whichever, a cursor is indeed unnecessary and probably bad
Trey, thanks for helping me clarify. Here goes...In the sample data below, I
have a lookup table with a list of people, and an Incoming table, with new
records. I need to be able to run a query whereby I search for either name,
address, or phone in the lookup table, and compare the records in the
Incoming table to ensure the incoming data is accurate.
Using the data below, let's assume I am searching by name. The results of
this run of the "solution" would return a hit on the Bob row, as the
incoming address does not match what is in the lookup table.
My plan was to cursor through the lookup table, fetching rows from the
incoming data table, and generate my report output into a temp table, then
return the contents of the temp table.
Does this help clarify my issue?
Lookup Table
Name Address Phone
Bob Maine 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392
Incoming Table
Name Address Phone
Bob Georgia 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392|||You're approaching the problem from a procedural perspective; try to
think relationally. You do not need a cursor for this; a simple SQL
join will do it for you.
SELECT inc.Name, inc.Address, inc.Phone
FROM Incoming inc LEFT JOIN Reference ref
ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
ref.Phone
WHERE ref.Name is NULL
You can use NOT EXISTS as suggested by Trey above, and it may perform
faster; I'm just used to reading LEFT JOIN's.
HTH,
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
>
Stu, this was our option #2, I just wasn't sure it was a good idea from
performance angle, so I thought I'd avoid it. My concern is that the
customer wants to be able to do the comparison via different comparisons of
fields, by name, by address, etc. So I'll need to write different
statements, which is not a big deal, I am just worried that if one of these
fields is not indexed, and I am joining millions of rows to millions of
rows...what will that do to the server?
BV.|||Stu wrote:
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
> HTH,
> Stu
instead of joining the tables and compare the cols it is sometimes
easieer (especially with many many columns to compare) to use the
checksum() functions of SQL Server.
you can do things like
select * from incoming x where checksum(name,address,phone) in (select
checksum(name,address,phone) from lookup)
to compare many many rows very easily. I dont know if this violates some
best practices or something, but checksum() helped me out very often.
hth
Gregor Stefka|||As long as you are joining on the key fields every time, performance should
not be an issue with this approach. I am assuming that the key fields have
to be equal before you begin looking at any other criteria. Adding extra
criteria should not prevent you from using indexes (assuming the extra
criteria does not include an additional table).
"BenignVanilla" <bvanilla@.tibetanbeefgarden.com> wrote in message
news:JOudnY6-w907uFjeRVn-sw@.giganews.com...
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> Stu, this was our option #2, I just wasn't sure it was a good idea from
> performance angle, so I thought I'd avoid it. My concern is that the
> customer wants to be able to do the comparison via different comparisons
of
> fields, by name, by address, etc. So I'll need to write different
> statements, which is not a big deal, I am just worried that if one of
these
> fields is not indexed, and I am joining millions of rows to millions of
> rows...what will that do to the server?
> BV.
>|||Performance tuning is always an issue, especially with a very large
database; however, a JOIN will ALWAYS perform better than a cursor.
I'm not saying that cursors don't have their place; this just ain't one
of them.
If you're worried about performance, be sure that your indexes are
onthe appropriate joining columns, and you may consider reducing your
isolation level (since it sounds as if you are reporting on batched
data, rather than live data).
Stu