Inner Join Query

I'm having difficulty figuring out how Inner Join's work. I have read
some online tutorials, but have not been able to craft the inner join
query that I need.

I have a table: PersonLocale that contains multiple City/State entries
for each PersonID:


PersonID | City | State
1 | Charlotte | NC
1 | Raleigh | NC
1 | Durham | NC
2 | Chicago | IL
2 | Memphis | TN


I'm trying to write a query that will count the number of entries per
state. So the resulting query would give me:

NC | 3
IL | 1
TN | 1

I'll need to compare this result to another table, but would like to
figure out how to do part 1 before looking at part 2.

Thanks for your help

Greg Scharlemann wrote:
> PersonID | City | State
> 1 | Charlotte | NC
> 1 | Raleigh | NC
> 1 | Durham | NC
> 2 | Chicago | IL
> 2 | Memphis | TN
>
>
> I'm trying to write a query that will count the number of entries per
> state. So the resulting query would give me:
>
> NC | 3
> IL | 1
> TN | 1
>
> I'll need to compare this result to another table, but would like to
> figure out how to do part 1 before looking at part 2.

IIRC,

SELECT State, COUNT(PersonID) FROM PersonLocale GROUP BY State

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org

On Dec 1, 10:44 pm, Nicholas Sherlock <N.sherl...@gmail.com> wrote:
>
> SELECT State, COUNT(PersonID) FROM PersonLocale GROUP BY State
>

Wow, I was making it much harder than it needed to be. Let's suppose
there's a second table Transaction. The Transaction table stores all of
the Persons transaction information. Person can have multiple
transactions, but is not required to have a Transaction.

Transaction Table:
TransactionID | PersonID | Details
1 | 1 ...
2 | 1
3 | 3


PersonID | City | State
1 | Charlotte | NC
1 | Raleigh | NC
1 | Durham | NC
2 | Chicago | IL
2 | Memphis | TN
3 | New York | NY

The resulting table should only include the total number of Persons who
have completed a transaction in the given state:

NC | 1
NY | 1

I guess this would require some sort of Join to join the PersonID in
the PersonLocale table with the PersonID in the transaction table?
Thanks for the direction!

Greg Scharlemann wrote:
> Transaction Table:
> TransactionID | PersonID | Details
> 1 | 1 ...
> 2 | 1
> 3 | 3
>
>
> PersonID | City | State
> 1 | Charlotte | NC
> 1 | Raleigh | NC
> 1 | Durham | NC
> 2 | Chicago | IL
> 2 | Memphis | TN
> 3 | New York | NY
>
> The resulting table should only include the total number of Persons who
> have completed a transaction in the given state:
>
> NC | 1
> NY | 1

Maybe:

SELECT State, COUNT(DISTINCT PersonLocale.PersonID) FROM PersonLocale
INNER JOIN TransactionTable ON
(PersonLocale.PersonID=TransactionTable.PersonID) GROUP BY State

But with your schema, you wouldn't know which state the transaction was
completed in if a person is linked to multiple states.

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org