SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to fetch records from multiple tables


how to fetch records from multiple tables

Author
Message
manibad
manibad
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 143
i have Demo,elections and electionshist tables where i should neglect the pepole who have status='t' from demo table and for those who are not neglected i should check whether electionstartdate is null and electionterminationdate is not null from election table then for these people i should check in electionhist table that whether the person has electionstartdate is not null(the value must be fetched from the top most load.
In demo and election tables we have nearly 50000 -60000 records but in electionhist tables nearly 10000000 records are there.
Can anyone please suggest me in pulling records when a situation prevails like this....i am able to do it but it takes a longer time....i need to pull it in mins.
I have given you the entire picture of the situation..Pleas go through this and let me know your suggestions.Thanks in advance

CREATE TABLE Demo
(
Sno bigint,(PK,IC)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
status varchar(5)
)INDEX(Gidempno)

CREATE TABLE Election
(
SSno bigint,(PK,IC)
Sno bigint,(FK_demo)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
Type varchar(6),
electionstartdate datetime(8),
electiontermdate datetime(8)
)
INDEX(Gidlidtype)

CREATE TABLE Electionhist
(
SSSno bigint,(PK,IC)
SSno bigint,(FK_election)
Sno bigint,(FK_demo)
GId bigint,(FK_clt)
lid bigint,(Fk_ld)
Slid bigint,(Fk_Sld)
eno varchar(15),
electionstartdate datetime(8),
electiontermdate datetime(8)
)

Create table clt
(
sid int,[PK,iC]
gid bigint,
clid bigint
)

Create table ld
(
Sid int,[pk,ic]
lid bigint,
gid bigint
)

Create table sld
(
sid int,[PK,IC]
slid bigint,
lid bigint,
gid bigint
)

PK->primary key
IC->Identity column
FK_XXX->Foregin key_Refereneced table


Pic of the table

Demo
Sno Gid Lid Slid Eno Status
6 123 9876 546 765 A
7 123 9876 546 546 R
8 123 9876 546 321 T

Election
Ssno Sno Gid Lid Slid Eno type ElectionStartdate Electiontermdate
10 6 123 9876 546 765 S NULL 3/2/2012
11 6 123 9876 546 765 L NULL 3/2/2012
12 7 123 9876 546 546 S 3/2/2012 NULL

Electionhist
SSSno Ssno Sno Gid Lid Slid Eno type ElectionStartdate Electiontermdate
25 2 1 123 1000 23 765 S NULL 3/2/2010
26 3 1 123 1090 25 765 S 9/9/2009 NULL
27 4 2 123 1090 25 765 L 9/9/2009 NULL
28 5 3 123 1101 87 321 S NULL NULL
29 8 4 123 1190 89 765 S 9/9/2009 NULL
30 9 5 123 1190 89 765 L 9/9/2009 NULL

Query what i have tried:

select seh.GID , seh.eno, seh.type, Max(seh.LID) LID,MAX(seh.slid) Slid
FROM dbo.clt g WITH(NOLOCK)
CROSS APPLY(select seh.gid,seh.LID,seh.Slid,seh.SSN,seh.type from dbo.Electionhist seh WITH (NOLOCK)
INNER JOIN dbo.clt g with(nolock)
on g.GID=seh.GID
AND g.ClID=90
INNER JOIN dbo.Ld l with(nolock)
on l.GID=g.GID
and l.LID=seh.LID
INNER JOIN dbo.SLd sl with (nolock)
ON sl.gid=l.gid
AND sl.LID=l.LID
AND sl.SLID=seh.SLID
INNER JOIN dbo.demo s WITH(NOLOCK, INDEX(IX_demo_GIDeno))
ON s.gid >'0'
AND s.eno > '0'
AND s.GID=sl.GID
AND s.GID=seh.GID
AND s.eno=seh.eno
AND s.LID=sl.LID
AND s.SLID=sl.SlID
AND s.Status <>'T'
INNER JOIN dbo.Election se WITH (NOLOCK,INDEX (IX_Election_GIDLIDtype))
ON se.GID >'0'
AND se.LID > '0'
AND se.PlType in('S','L')
AND se.GID=s.GID
AND se.GID=seh.GID
AND se.LID=s.LID
AND se.SLID=s.sldid
and se.electionStartDate is null
and se.electionTerminationDate is not null

WHERE seh.gid >'0'
AND seh.LID > '0'
AND seh.type in('S','L')
AND seh.electionStartDate IS NOT NULL
AND seh.electionTerminationDate IS NULL
)
group by seh.GID , seh.eno, seh.type


Expected Result
GID eno Type LID Slid
123 1190 89 765 S
123 1190 89 765 L

If you are not clear with this please let me know i will explain you more.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28459 Visits: 13268
CELKO (12/27/2012)


Please learn the ISO-11179 rules for data element names.


Which can be downloaded where?

CELKO (12/27/2012)

I guess PK means “NOT NULL PRIMARY KEY”, but a good SQL programmer never uses IDENTITY for a key.


That's hilarious. Care to explain why?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5514 Visits: 4076
Also please post exec plan(people would prefer graphical plan :-P ) along with index definition too as index play significant role to pull out the data faster.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27208 Visits: 17557
You also might want to read up on the NOLOCK hint. It can produce all sorts of really difficult to replicate bugs. It can (and will) miss some rows and even return duplicates.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8334 Visits: 7163
Bhuvnesh (12/27/2012)
Also please post exec plan(people would prefer graphical plan :-P ) along with index definition too as index play significant role to pull out the data faster.



Agreed. The indexes on Electionhist will be especially critical to performance. If you've got the "default", no-thought identity-only clustered index, performance will never be that good.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27208 Visits: 17557
Joe I certainly understand your position but I do have a question. You continually refer to Cindy Lou Who as employee #42. In a Joe Celko database what would be used for the primary key in this table?

You can't use SSN. That would be a reasonable choice at first glance but there are two MAJOR flaws with that. First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool. Secondly, and probably far more important, is that SSN is sensitive information. Anytime you have SSN in your database it should be in one and only one location and it should be encrypted at rest. This pretty much rules out SSN as a valid primary key. What else is there? Name certainly doesn't do it.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8334 Visits: 7163
Sean Lange (12/28/2012)
Joe I certainly understand your position but I do have a question. You continually refer to Cindy Lou Who as employee #42. In a Joe Celko database what would be used for the primary key in this table?

You can't use SSN. That would be a reasonable choice at first glance but there are two MAJOR flaws with that. First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool. Secondly, and probably far more important, is that SSN is sensitive information. Anytime you have SSN in your database it should be in one and only one location and it should be encrypted at rest. This pretty much rules out SSN as a valid primary key. What else is there? Name certainly doesn't do it.


Not to mention that some employees may not even have SSNs: foreign representatives, etc..

An identity is clearly applicable to many things, including (but not limited to) employee numbers and order numbers. Obviously you create a unique constraint of some type to guarantee uniqueness.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
patrickmcginnis59
patrickmcginnis59
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 2333
CELKO (12/27/2012)

I guess PK means “NOT NULL PRIMARY KEY”, but a good SQL programmer never uses IDENTITY for a key.


That's hilarious. Care to explain why?


I would like to second this evaluation. IDENTITY for a table's primary key is out there as an accepted practice, especially when there's no suitable natural key. Surely there's some good SQL programmers out there doing exactly what CELKO says they do not do.

edit: quote tag
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search