Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to fetch records from multiple tables

Author  Topic 

manibad
Starting Member

9 Posts

Posted - 2012-12-22 : 20:30:27
i have Demo,elections and electionshist tables where i should neglect pepole who have status='t' from demo table and for those people i should check whether electionstartdate is null and electionterminationdate is not null from election table then for those people i shld chk in electionhist table that he 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 so long time....i need to pull it in mins.Please guide me in this...

manibad
Starting Member

9 Posts

Posted - 2012-12-23 : 00:19:59
people are you able to understand my requirement?or shall i explain it clearly...?
please post your reply:)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-23 : 01:36:52
can you show what you tried yet?
it seems to be a simple join between involved tables with logic on electionhist table to get latest record. for that you can refer to below post scenario 2

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

make a try first and post in case you face any issues and then we will help out



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manibad
Starting Member

9 Posts

Posted - 2012-12-23 : 14:50:52
select seh.GID , seh.empno, 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_GIDempno))
ON s.gid >'0'
AND s.empno > '0'
AND s.GID=sl.GID
AND s.GID=seh.GID
AND s.empno=seh.empno
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.empno, seh.type

Here GID,Lid,Slid are id's from different tables which are referenced to demo,election and electionhist...
then clt is an stationary table where we find description abt gid's...


Acc to my guess record fetching takes time because of these inclusion of id's from referenced tables...but these are referenced to maintain integrity within our system.So we can't neglect it too

FYI i am an beginner so i am not sure how far this query is correct..please teach me to rectify mistakes in this.

If you require more info please tell me i will explain you mmuch more than this.
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-24 : 15:22:14
can you post some sample data from tables and then explain your exact output? 5-10 records from each table would be enough. see below to understand how to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

manibad
Starting Member

9 Posts

Posted - 2012-12-25 : 20:28:52

I need to find the record who has ElectionStartdate is null and electiontermdate is not null in the Election table
but the same person has electionstartdate is not null and electiontermdate is null in Electionhist table.In hist i need to pull the latest record satisfying the given condition

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 pls let me knopw i will explain you more.
Go to Top of Page
   

- Advertisement -