Results 1 to 5 of 5

Thread: How To : SELECT

  1. #1
    K Yegneshwara Iyer Guest

    How To : SELECT


    Hi

    This is a pretty long question - so please bear with me.

    I'm an SQL beginner and need help with what looks like a very complex query to me. I know how to accomplish the task with a procedural language but would like to learn the SQL way. I have seen the hundred SQL tutorials on various web sites and have read a book or two on SQL - but it looks like I need more help.

    I have a few normalised tables

    1. STATE
    Columns : STATECODE
    STATENAME
    Sample Data :
    30 ABC
    31 DEF
    32 GHI
    33 JKL

    2. PARTY
    Columns : PARTYCODE
    PARTYNAME
    PARTYSYMBOL
    Sample Data :
    AWP All-World-Party Elephant
    AAP All-Asia-Party Cow


    3. CONSTITUENCY
    Columns : CONSTCODE
    STATECODE
    CONSTNAME
    Sample Data :
    30004 30 My Constituency
    30005 30 Your constituency
    30006 30 Our Constituency
    31001 31 Their Constituency
    31002 32 His Constituency
    4. LEADS
    Columns : CONSTCODE
    LEADPARTYCODE
    Sample Data :
    30004 AAP
    30005 AAP
    31001 AAP
    31002 AAP
    30005 AAP
    30006 AWP

    This is an election database. The LEADS table has one row per constituency for all constituencies in the CONSTITUENCY table. Yes, I may not need a separate LEADS table - but that is another story.

    I want to find out the party position state wise. Using a procedural language, I would first query the state table for a list of constituencies in state "30". Then I would group the LEADS table by LEADPARTYCODE for all those constituencies. Then I would pick up names and symbols for all the parties.

    I guess this can be done more efficiently than creating 3 different recordsets and looping through the data.

    A sample result set that I would like is

    STATENAME PARTYCODE PARTYNAME PARTYSYMBOL LEADING_IN_#
    ABC AAP All-Asia-Party Cow 3
    ABC AAW All-World-Party Elephant 1


    Thanks for reading all this.

    Can somebody, please, help me with how to work this out ?

    Thanks a million

    Regards

    Iyer

  2. #2
    Guest

    How To : SELECT (reply)




    ------------
    K Yegneshwara Iyer at 6/24/01 6:28:14 AM


    Hi

    This is a pretty long question - so please bear with me.

    I'm an SQL beginner and need help with what looks like a very complex query to me. I know how to accomplish the task with a procedural language but would like to learn the SQL way. I have seen the hundred SQL tutorials on various web sites and have read a book or two on SQL - but it looks like I need more help.

    I have a few normalised tables

    1. STATE
    Columns : STATECODE
    STATENAME
    Sample Data :
    30 ABC
    31 DEF
    32 GHI
    33 JKL

    2. PARTY
    Columns : PARTYCODE
    PARTYNAME
    PARTYSYMBOL
    Sample Data :
    AWP All-World-Party Elephant
    AAP All-Asia-Party Cow


    3. CONSTITUENCY
    Columns : CONSTCODE
    STATECODE
    CONSTNAME
    Sample Data :
    30004 30 My Constituency
    30005 30 Your constituency
    30006 30 Our Constituency
    31001 31 Their Constituency
    31002 32 His Constituency
    4. LEADS
    Columns : CONSTCODE
    LEADPARTYCODE
    Sample Data :
    30004 AAP
    30005 AAP
    31001 AAP
    31002 AAP
    30005 AAP
    30006 AWP

    This is an election database. The LEADS table has one row per constituency for all constituencies in the CONSTITUENCY table. Yes, I may not need a separate LEADS table - but that is another story.

    I want to find out the party position state wise. Using a procedural language, I would first query the state table for a list of constituencies in state "30". Then I would group the LEADS table by LEADPARTYCODE for all those constituencies. Then I would pick up names and symbols for all the parties.

    I guess this can be done more efficiently than creating 3 different recordsets and looping through the data.

    A sample result set that I would like is

    STATENAME PARTYCODE PARTYNAME PARTYSYMBOL LEADING_IN_#
    ABC AAP All-Asia-Party Cow 3
    ABC AAW All-World-Party Elephant 1


    Thanks for reading all this.

    Can somebody, please, help me with how to work this out ?

    Thanks a million

    Regards

    Iyer

  3. #3
    todd Guest

    How To : SELECT (reply)

    SELECT A.STATENAME,
    D.PARTYCODE,
    D.PARTYNAME,
    D.PARTYSYMBOL,
    COUNT(*) AS "LEADING_IN_#"
    FROM STATE A
    JOIN CONSTITUENCY B
    ON A.StateCode = B.StateCode
    JOIN LEADS C
    ON C.CONSTCODE = B.CONSTCODE
    JOIN PARTY D
    ON C.LEADPARTYCODE = d.PARTYCODE
    GROUP BY A.STATENAME,
    D.PARTYCODE,
    D.PARTYNAME,
    D.PARTYSYMBOL


    ------------
    K Yegneshwara Iyer at 6/24/01 6:28:14 AM


    Hi

    This is a pretty long question - so please bear with me.

    I'm an SQL beginner and need help with what looks like a very complex query to me. I know how to accomplish the task with a procedural language but would like to learn the SQL way. I have seen the hundred SQL tutorials on various web sites and have read a book or two on SQL - but it looks like I need more help.

    I have a few normalised tables

    1. STATE
    Columns : STATECODE
    STATENAME
    Sample Data :
    30 ABC
    31 DEF
    32 GHI
    33 JKL

    2. PARTY
    Columns : PARTYCODE
    PARTYNAME
    PARTYSYMBOL
    Sample Data :
    AWP All-World-Party Elephant
    AAP All-Asia-Party Cow


    3. CONSTITUENCY
    Columns : CONSTCODE
    STATECODE
    CONSTNAME
    Sample Data :
    30004 30 My Constituency
    30005 30 Your constituency
    30006 30 Our Constituency
    31001 31 Their Constituency
    31002 32 His Constituency
    4. LEADS
    Columns : CONSTCODE
    LEADPARTYCODE
    Sample Data :
    30004 AAP
    30005 AAP
    31001 AAP
    31002 AAP
    30005 AAP
    30006 AWP

    This is an election database. The LEADS table has one row per constituency for all constituencies in the CONSTITUENCY table. Yes, I may not need a separate LEADS table - but that is another story.

    I want to find out the party position state wise. Using a procedural language, I would first query the state table for a list of constituencies in state "30". Then I would group the LEADS table by LEADPARTYCODE for all those constituencies. Then I would pick up names and symbols for all the parties.

    I guess this can be done more efficiently than creating 3 different recordsets and looping through the data.

    A sample result set that I would like is

    STATENAME PARTYCODE PARTYNAME PARTYSYMBOL LEADING_IN_#
    ABC AAP All-Asia-Party Cow 3
    ABC AAW All-World-Party Elephant 1


    Thanks for reading all this.

    Can somebody, please, help me with how to work this out ?

    Thanks a million

    Regards

    Iyer

  4. #4
    K Y Iyer Guest

    How To : SELECT (reply)

    Todd

    THANKS A MILLION. I'll try to get this running and _more importantly_ try to understand it !!

    Warm regards

    Iyer

    ------------
    todd at 6/25/01 8:52:17 AM

    SELECT A.STATENAME,
    D.PARTYCODE,
    D.PARTYNAME,
    D.PARTYSYMBOL,
    COUNT(*) AS "LEADING_IN_#"
    FROM STATE A
    JOIN CONSTITUENCY B
    ON A.StateCode = B.StateCode
    JOIN LEADS C
    ON C.CONSTCODE = B.CONSTCODE
    JOIN PARTY D
    ON C.LEADPARTYCODE = d.PARTYCODE
    GROUP BY A.STATENAME,
    D.PARTYCODE,
    D.PARTYNAME,
    D.PARTYSYMBOL


    ------------
    K Yegneshwara Iyer at 6/24/01 6:28:14 AM


    Hi

    This is a pretty long question - so please bear with me.

    I'm an SQL beginner and need help with what looks like a very complex query to me. I know how to accomplish the task with a procedural language but would like to learn the SQL way. I have seen the hundred SQL tutorials on various web sites and have read a book or two on SQL - but it looks like I need more help.

    I have a few normalised tables

    1. STATE
    Columns : STATECODE
    STATENAME
    Sample Data :
    30 ABC
    31 DEF
    32 GHI
    33 JKL

    2. PARTY
    Columns : PARTYCODE
    PARTYNAME
    PARTYSYMBOL
    Sample Data :
    AWP All-World-Party Elephant
    AAP All-Asia-Party Cow


    3. CONSTITUENCY
    Columns : CONSTCODE
    STATECODE
    CONSTNAME
    Sample Data :
    30004 30 My Constituency
    30005 30 Your constituency
    30006 30 Our Constituency
    31001 31 Their Constituency
    31002 32 His Constituency
    4. LEADS
    Columns : CONSTCODE
    LEADPARTYCODE
    Sample Data :
    30004 AAP
    30005 AAP
    31001 AAP
    31002 AAP
    30005 AAP
    30006 AWP

    This is an election database. The LEADS table has one row per constituency for all constituencies in the CONSTITUENCY table. Yes, I may not need a separate LEADS table - but that is another story.

    I want to find out the party position state wise. Using a procedural language, I would first query the state table for a list of constituencies in state "30". Then I would group the LEADS table by LEADPARTYCODE for all those constituencies. Then I would pick up names and symbols for all the parties.

    I guess this can be done more efficiently than creating 3 different recordsets and looping through the data.

    A sample result set that I would like is

    STATENAME PARTYCODE PARTYNAME PARTYSYMBOL LEADING_IN_#
    ABC AAP All-Asia-Party Cow 3
    ABC AAW All-World-Party Elephant 1


    Thanks for reading all this.

    Can somebody, please, help me with how to work this out ?

    Thanks a million

    Regards

    Iyer

  5. #5
    Join Date
    Jul 2010
    Posts
    1

    A big thank you!

    Todd

    I am not sure if you'll read this - but for the sake of posterity, here's my heartfelt gratitude for your assistance 9 years ago.

    Your query changed our lives - and we built what is considered the fastest elections systems in India - we do not say that - our competitors do.

    Once again, thanks a million.

    Hope you get to read this after nine years,

    Cheers

    Iyer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •