I have a MySQL table: (pseudo-code) ListingFieldData(ParentListingID INT, Value LONGTEXT) A basic search queries the Value, and returns all of the ParentListingIDs relevant to the query. (There may be 30+ ListingFieldData rows per each ParentListingID - and possibly thousands of "Parent Listings" - meaning potentially hundreds of thousands of ListingFieldData rows in total.) * The problem is that the search only needs to return each ParentListingID once, instead of hundreds of thousands of times. I've experimented with both the "DISTINCT" and "GROUP BY" keywords, both to no avail. The GROUP BY is the closest I've gotten, but it doesn't seem to work the way I need it to (I'm probably using it wrong). I have a feeling that I'm grossly overlooking something...something simple... Question) is there a way to get MySQL to return only "distinct" ParentListingIDs, skipping ParentListingIDs that have already been found? Perhaps there is some sort of "DISTINCT COLUMN" functionality? (Note: I need this operation to be performed MySQL-server-side, instead of "manually" trimming duplicate ListingFieldIDs with a PHP routine) Thanks for any help. [Edited by - swordfish on September 29, 2007 8:20:13 PM]

I think DISTINCT works as advertised - not sure what went wrong.
Either way, I'm still having issues.

Here's the query:

(A) SELECT SQL_BIG_RESULT DISTINCT(listingID) FROM vamm_v1a1_listing_field_data

The above returns only 1 listingID, which appears to either be the very last listingID, or the count of all of the listingIDs. I'm 100% sure that there are more than 1 distinct listingIDs.

The above is a "browse all" type of search.

On the other hand, if I add WHERE criteria to the query, the situation is drastically different:

(B) SELECT SQL_BIG_RESULT DISTINCT(listingID) FROM vamm_v1a1_listing_field_data WHERE (sectionFieldID='1' AND value_string='Existing')

That last query (B) appears to work the way I want it to, returning thousands of distinct listingIDs. Perfect, but (A) is still flawed. At this point, I can workaround (A) and just simply fetch all of the listingIDs directly from the Listings table, but I'd still like to solve (A) for peace of mind.

What am I not seeing?

P.S.
Another test:

(C) SELECT SQL_BIG_RESULT listingID FROM vamm_v1a1_listing_field_data GROUP BY listingID

(C) appears to have the same result as (A)...

Also, if I remove the SQL_BIG_RESULT keyword from the query, the search yields a 500 Internal Server Error (which is my original problem; and none of the php.ini directives remedies the problem).

[Edited by - swordfish on September 29, 2007 10:18:00 PM]

I'm issuing the commands via PHP.
I've been trying to get ahold of my client for the past few days so I can access their PhpMyAdmin, but communication is slow.

MySQL 5.0.18, PHP 4.3.11 (GoDaddy Shared Hosting)

I originally tried the listingID=listingID as illustrated by a site found on Google, and it doesn't seem to make any difference.

I can get rid of the 500 Internal Server Error by throwing a LIMIT into the mix.
What does this mean? Perhaps I'm running out of memory? I tried boosting the max_execution_time, mysql.connect_timeout, and memory_limit directives, nothing works.

I'll try to issue the commands via PhpMyAdmin whenever I get a chance.