Professional Web Applications Themes

Inner Join Query - MySQL

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

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Inner Join Query

    Greg Scharlemann wrote: 

    IIRC,

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

    Cheers,
    Nicholas Sherlock

    --
    http://www.sherlocksoftware.org
    Nicholas Guest

  3. #3

    Default Re: Inner Join Query


    On Dec 1, 10:44 pm, Nicholas Sherlock <com> wrote: 

    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 Guest

  4. #4

    Default Re: Inner Join Query

    Greg Scharlemann wrote: 

    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
    Nicholas Guest

Similar Threads

  1. query/right join/
    By Coldfusionstudent in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 1st, 05:22 PM
  2. sql join query
    By dylan in forum MySQL
    Replies: 8
    Last Post: August 24th, 12:07 PM
  3. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139