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.

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 wrote:
>
> SELECT P.PersonID FROM PersonLocale PL
> LEFT JOIN Person P ON P.PersonID = PL.PersonID
> WHERE State = 'NC'
> ORDER BY LastName,FirstName

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.scharlem...@gmail.com wrote:
> strawberry wrote:
> >
> > SELECT P.PersonID FROM PersonLocale PL
> > LEFT JOIN Person P ON P.PersonID = PL.PersonID
> > WHERE State = 'NC'
> > ORDER BY LastName,FirstName
>
> 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?

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 wrote:
> greg.scharlem...@gmail.com wrote:
> > strawberry wrote:
> > >
> > > SELECT P.PersonID FROM PersonLocale PL
> > > LEFT JOIN Person P ON P.PersonID = PL.PersonID
> > > WHERE State = 'NC'
> > > ORDER BY LastName,FirstName
> >
> > 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?
>
> I think it's showing an AgentID that is all zeros (00000000) as NULL.
> Not sure why it's listing it twice though.

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