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

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

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

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

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