How about something like this?
SELECT Site.Site_ID, Site, Status, Type
WHERE EXISTS( SELECT * FROM Project)
ORDER BY Site;
I'm assuming Site_ID is unique in the Site table?
From: Stephen P. Fracek, Jr. [mailto:sfracek@stripped]
Sent: Monday, May 01, 2006 1:27 PM
Subject: Re: Optimizing DISTINCT searches
On 2006-05-01 1:14 PM, "Robert DiFalco" <rdifalco@stripped> wrote:
> Would you need the DISTINCT if you change the query like so?
> SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON
> Site.Site_ID = Project.Site_ID ORDER BY Site;
> You may also want to just try your initial query without the distinct
> to see if that is the issue. Also, do you have an index on the "Site"
> column? The issue with this query is that you are pretty much
> selecting everything from the Project table.
Your query doesn't work - it finds ALL the rows in Project table and
hence repeats the sites..
I do have an index on the Site table, it is the Site_ID. The
Project.Site_ID is also indexed.
Stephen P. Fracek, Jr.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql