Professional Web Applications Themes

Best way to use subquery? - MySQL

There are two tables. Person and PersonLocales. Person has PersonID, LastName, FirstName and other columns. PersonLocale has PersonID, City, State. A Person can have an unlimited number of PersonLocales as long as each row in PersonLocale for the defined PersonID is unique. I'm trying to return all of the PersonID's in a specific state ordered by the LastName, but I can't quite get my subquery to work. SELECT distinct PersonLocales.PersonID FROM PersonLocales WHERE State = 'NC' AND PersonLocales.PersonID = ( SELECT Person.PersonID FROM Person where PersonLocales.PersonID = Person.PersonID ORDER BY Person.LastName ) LIMIT 0 , 30 The problem I am ...

  1. #1

    Default Best way to use subquery?

    There are two tables. Person and PersonLocales.
    Person has PersonID, LastName, FirstName and other columns.
    PersonLocale has PersonID, City, State.

    A Person can have an unlimited number of PersonLocales as long as each
    row in PersonLocale for the defined PersonID is unique. I'm trying to
    return all of the PersonID's in a specific state ordered by the
    LastName, but I can't quite get my subquery to work.

    SELECT distinct PersonLocales.PersonID FROM PersonLocales WHERE
    State = 'NC' AND PersonLocales.PersonID = (
    SELECT Person.PersonID FROM Person
    where PersonLocales.PersonID = Person.PersonID
    ORDER BY Person.LastName
    )
    LIMIT 0 , 30

    The problem I am getting is the above query returns a row containing a
    PersonID for each entry in the PersonLocales.

    1. How do I eliminate the duplicate rows? My distinct statement doesn't
    seem to do the trick.
    2. Is there a better way to do this query from a database perspective?

    Thanks for your help.

    greg.scharlemann@gmail.com Guest

  2. #2

    Default Re: Best way to use subquery?

    Don't use a subquery - and don't have duplicate rows!

    Use a JOIN instead, something like (untested):

    SELECT P.PersonID FROM PersonLocale PL
    LEFT JOIN Person P ON P.PersonID = PL.PersonID
    WHERE State = 'NC'
    ORDER BY LastName,FirstName

    strawberry Guest

  3. #3

    Default Re: Best way to use subquery?


    strawberry wrote: 

    Thanks, but the first two records that I get a NULL and I none of the
    columns that I'm quering on have a NULL value. Any idea why?

    greg.scharlemann@gmail.com Guest

  4. #4

    Default Re: Best way to use subquery?


    com wrote: 
    >
    > Thanks, but the first two records that I get a NULL and I none of the
    > columns that I'm quering on have a NULL value. Any idea why?[/ref]

    I think it's showing an AgentID that is all zeros (00000000) as NULL.
    Not sure why it's listing it twice though.

    greg.scharlemann@gmail.com Guest

  5. #5

    Default Re: Best way to use subquery?


    com wrote: 
    > >
    > > Thanks, but the first two records that I get a NULL and I none of the
    > > columns that I'm quering on have a NULL value. Any idea why?[/ref]
    >
    > I think it's showing an AgentID that is all zeros (00000000) as NULL.
    > Not sure why it's listing it twice though.[/ref]

    I got it. My tables were not correctly aligned. I had deleted a Person
    but did not remove the PersonID from the PersonLocale table.

    greg.scharlemann@gmail.com Guest

Similar Threads

  1. Avoiding subquery
    By André Hänsel in forum MySQL
    Replies: 3
    Last Post: February 17th, 06:48 AM
  2. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  3. using list in subquery
    By spacehog in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: March 9th, 11:23 PM
  4. SQL subquery question
    By derek in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:48 PM
  5. Select From SubQuery
    By SriSamp in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 09:28 AM

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