how to fetch records from multiple tables

  • 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.

  • 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?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Also please post exec plan(people would prefer graphical plan 😛 ) 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;-)

  • 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/[/url]

    _______________________________________________________________

    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/

  • Bhuvnesh (12/27/2012)


    Also please post exec plan(people would prefer graphical plan 😛 ) 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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 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/

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

  • No offense Joe but I think you have lost your mind if you actually think that Amazon uses the customer email as part of the primary key. How many supporting tables would that value be needed as a foreign key? We all know that varchar is not a great candidate for performance when the length can be all over the place like an email. Given that the user is allowed to change their email at any time it goes against the notion that a primary key not change. Can you imagine how ridiculous it would be to change your email? There is not a chance on this earth that Amazon uses that as part of a primary key. They would be better off using the person's name instead of email, most people's names never change unlike their email address. If they used email as part of the key they would just about be forced to use an update trigger to update all the other tables in their system that need that value. We can also be pretty sure that they don't have an update trigger on the account table to propagate foreign key values all over the place.

    I agree that identity is probably overused but to say that it has no place in the real world is just flat out rubbish. To use your own arguments about turning Cindy Lou Who upside down to verify an identity, the same is true of SSN and email. They are no more a part of the physical entity than some arbitrary number.

    _______________________________________________________________

    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/

  • CELKO (12/28/2012)


    Usually I have to identify a person by the role they play in the Universe of Discourse. Most often the role does actually involve a person; a bank account number is universal (part of the IBAN, used by the Fed Reserve, etc) and can belong to a "lawful person", such as corporations, organization, etc.

    For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more

    I am with you on the theoretical aspects of this discussion but I have a hard time wrapping my head around the practical application aspects. In this example how would you model the primary key in the parent-table that stores people from which most people-attribute tables might be related? Assuming email is stored as a CHAR(320) and password is stored as a one-way hash in the database, let's say in a CHAR(100), then are you saying your primary key would be 420 bytes wide? Would you use a variable-length type like VARCHAR for email to save on some of the storage? What would happen if the email did change to something wider than before?

    All of that would seem impractical when compared to a 4-byte INT, or in the case of Amazon likely an 8-byte BIGINT would be required, regardless of how it might fly in the face of the theory that a machine's idea of a physical insert attempt is an invalid key to identify a person. Or do you use a checksum or hash algorithm of some sort on the combination of email and password to reduce the size while maintaining uniqueness?

    Even in the case of a bank account number you'll have a 34-byte key which could weigh down internal data processing when compared to an 8-byte BIGINT.

    Bottom line question: what actual value would you store in the parent table to use in uniquely identifying a person, as well as to relate the parent person table other tables where that person's addresses (0-n) might be stored?

    As an aside, a SQL Server identity column's values in a database stored on serverA are portable to serverB. In the case where serverA dies and we restore the database to serverB from backups SQL Server will know where serverA left off in terms of the next number to issue from serverB.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • CELKO (12/28/2012)

    For a reasonable level of trust, I use the email address + password, like every website on earth. Seems to work well enough for Paypal, Amazon, et al. People do not change them much any more

    :laugh: :laugh: :laugh:

    That CAN'T be serious!! But sadly it is. HOLY IDIOCIES BATMAN! You have NO control over that.

    Yahoo and every other free provider AFAIK reclaims email addresses after a certain period of nonuse (say, after the user DIES, rather unavoidable for every user eventually!).

    So "JSmith2147" dies, his id is reassigned, and then you email his business info to someone else.

    You're one of those consultants that clearly sells only to the pointy-haired manager types who rely solely on perceived reputation and don't have a real clue about the actual implementation details of what you're spouting.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • CELKO (12/28/2012)


    Surely there's some good SQL programmers out there doing exactly what CELKO says they do not do.

    I have been at this for 25+ years now and I disagree. Can you give me a scenario where the physical insertion attempt count can have any meaning in a logical data model? How about the blink rate of the front panel of the disk drive?

    Back in the original 16-bit minicomputer days on UNIX in the 1970's, the hardware was slow, people did not understand RDBMS and this was assumed to be a way to speed up access. It kept joins short, etc. All you had to give up was data integrity and portability. But we did not know about those things back then. People thought they would always be on one platform, there were no standards and very little theory. And who would have a Terabyte of data on a small machine??

    Now move to the 21-st Century. 64-bit hardware, faster and bigger than all of the mainframes on Earth in 1970, SSD, ANSI/ISO Standards and decades of theory and research.

    Confession time: when I learned FORTRAN, we had six letter variable names. I got good at inventing six-letter variable names, so I did not stop doing this when I got better FORTRAN compilers and modern languages. I did not realize it was weird and made maintaining code almost impossible for years. :Whistling:

    So what do you use to key an Order header table if not Order#? ... After all, orders don't have email addresses!

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • CELKO (12/27/2012)


    but a good SQL programmer never uses IDENTITY for a key

    Another fine load of passive aggressive name calling hooie. Stop it or, since you seem incapable, just go away.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CELKO (12/28/2012)


    Can you give me a scenario where the physical insertion attempt count can have any meaning in a logical data model?

    Sure... but first, give us a good example of what you would design a Primary Key to be for a customer table and explain why.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sean Lange (12/28/2012)


    First of all, SSN have and will be reused. When people die their SSN gets recycled into the available pool.

    While it is true that a person can change their SSN, it's a myth that SSNs are recycled. Please see Q20 on the following webpage which is on the official Social Security website.

    http://www.socialsecurity.gov/history/hfaq.html

    There was one incident many years ago where some secretary had her SS card used as a demo card in wallets and some huge number of people claimed it as their own, but Social Security numbers have not been reused to date.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 22 total)

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