January 4, 2011 at 9:38 am
Hi,
I have two tables as below
CREATE TABLE [dbo].[Papers](
[PaperID] [int] NULL,
[RollNoID] [int] NULL,
[LiveRecID] [int] NULL,
[StudentID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[RollNo](
[Id] [int] NULL,
[StudentID] [int] NULL,
[NoOfPapers] [int] NULL
) ON [PRIMARY]
insert into rollno values (1,101,4)
insert into rollno values (2,102,2)
insert into papers values(1,1,0,101)
insert into papers values(2,1,0,101)
insert into papers values(3,1,0,101)
insert into papers values(4,1,0,101)
insert into papers values(5,1,1,101)
insert into papers values(6,2,0,102)
insert into papers values(7,2,0,102)
insert into papers values(8,2,6,102)
select * from papers
110101
210101
310101
410101
511101
620102
720102
826102
Now I want to display all the records, where if the LiveRecOID is available then do not display the corresponding PaperID.
eg. the second row is the clone of the first row, I identify it using the LiveRecOID column.
In the second row the value of LiveRecOID is 1, that means the clone of the PaperID value 1 has been created
110101
511101
So I need to display only the later and not the first one.
In other words, if the LiveRecOID is present, I should NOT display the row having the PaperID=LiveRecOID.
So My result should be as below.
210101
310101
410101
511101
720102
826102
I have the table having some 1 million rows, so kindly let me know the optimised query.
Thanks,
Regards
Hema
January 4, 2011 at 1:06 pm
Not really sure what the RollNo table has to do with this...
I think this is what you are looking for.
select p.*
from Papers p
left join papers p2 on p.PaperID = p2.LiveRecID
where p2.PaperID is null
Just as a side note if you have the ability to change the tables I would suggest dropping the RollNo table entirely. It looks like nothing more than a table storing the count of Papers for each StudentID. You could either calculate this on the fly when you actually need it or add a computed column to the Papers table.
You might also do some reading on "magic numbers" since this entire dataset seems to be comprised of nothing but magic keys.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply