[RESOLVED] Need some help with DISTINCT query

Shamrox

I am attempting to output a mailing list with the standard name, company, address, etc info, but a persons name should only show once.
The query below is taking a list of pubids, of which a persons names could appear on multiple pupid lists. The final output should only show their name once. I run the query against a few lists that i know the name shows on each to see if it returns their name only once, and it returns nothing. Could you please tell me if I have this written wrong and suggest a fix?

SELECT DISTINCT firstname, lastname, companyname, street1, street2, city, state, zipcode, zip4, country, countrycode, MAX(quantity) FROM moms_customer, moms_lists WHERE moms_customer.custnumber = moms_lists.custnumber AND pubid IN ('".$_POST['display']."') GROUP BY lastname

_RL_Ian

Shamrox wrote:
I am attempting to output a mailing list with the standard name, company, address, etc info, but a persons name should only show once.
The query below is taking a list of pubids, of which a persons names could appear on multiple pupid lists. The final output should only show their name once. I run the query against a few lists that i know the name shows on each to see if it returns their name only once, and it returns nothing. Could you please tell me if I have this written wrong and suggest a fix?
SELECT DISTINCT firstname, lastname, companyname, street1, street2, city, state, zipcode, zip4, country, countrycode, MAX(quantity) FROM moms_customer, moms_lists WHERE moms_customer.custnumber = moms_lists.custnumber AND pubid IN ('".$_POST['display']."') GROUP BY lastname

DISTINCT only returns 100% unique records. So you could have two records that have all the columns the exact same except one, and it is considered distinct.

From looking at your query, you have a lot of amibguity in your statement, meaning that you don't define which table some of your fields are from.

Take a look at this:

<?php
$sql = "SELECT DISTINCT c.firstname, c.lastname, c.companyname, c.street1, c.street2, c.city, c.state, c.zipcode, c.zip4, c.country, c.countrycode, MAX(l.quantity) 
		FROM `moms_customer` c, `moms_lists` l 
		WHERE c.custnumber = l.custnumber 
				AND l.pubid IN ('".$_POST['display']."') 
		GROUP BY c.lastname";
?>

I'm not sure if I matched up the tables and fields correctly, but you get the idea. Make sure that you specify which table (c or l) each field is from.

Not sure if that will fix your problem, but take a look at it and let me know.

Shamrox

While sitting and waiting for an answer, I stumbled across basically what you are talking about, adjusted my query and got the proper output. But thank you for taking the time to answer.

Shamrox

Add on:

Maybe someone can shed some light on what I'm doing wrong here.

When I run this query, I'm looking for a distinct custnumber (Id) for a mailing list, but i also need to extract the maximum quantity number that they might be requesting for any of the pubid's listed in the selection.

For some reason, it's leaving off some custnumbers that I know should be included based on the criteria.

HELP!!

_RL_Ian

Shamrox wrote:
Add on:

Maybe someone can shed some light on what I'm doing wrong here.

When I run this query, I'm looking for a distinct custnumber (Id) for a mailing list, but i also need to extract the maximum quantity number that they might be requesting for any of the pubid's listed in the selection.

For some reason, it's leaving off some custnumbers that I know should be included based on the criteria.

HELP!!

You need to GROUP BY custnumber instead of lastname.

Shamrox

Here's exactly how i have the statement now and it's still dropping some custnumbers that definitely should be on the output list.

SELECT DISTINCT(c.custnumber), c.firstname, c.lastname, c.companyname, c.department, c.street1, c.street2, c.city, c.state, c.zipcode, c.zip4, c.country, c.countrycode, MAX(l.quantity) FROM `moms_customer` c, `moms_lists` l WHERE c.custnumber = l.custnumber AND l.pubid IN ('".$_POST['display']."') GROUP BY c.custnumber

_RL_Ian

Shamrox wrote:
Here's exactly how i have the statement now and it's still dropping some custnumbers that definitely should be on the output list.
SELECT DISTINCT(c.custnumber), c.firstname, c.lastname, c.companyname, c.department, c.street1, c.street2, c.city, c.state, c.zipcode, c.zip4, c.country, c.countrycode, MAX(l.quantity) FROM `moms_customer` c, `moms_lists` l WHERE c.custnumber = l.custnumber AND l.pubid IN ('".$_POST['display']."') GROUP BY c.custnumber

SELECT DISTINCT c.custnumber, c.firstname, c.lastname, c.companyname, c.department, c.street1, c.street2, c.city, c.state, c.zipcode, c.zip4, c.country, c.countrycode, MAX(l.quantity) FROM `moms_customer` c, `moms_lists` l WHERE c.custnumber = l.custnumber AND l.pubid IN ('".$_POST['display']."') GROUP BY c.custnumber

Shamrox

Still drops them.

Shamrox

could it have anything to do with the MAX(quantity) part of the query? Like I'm asking the query to do to much at one time?

_RL_Ian

Oh, I didn't see that you were grouping in the 'l' table. Try grouping by l.custnumber.

It's hard to test without your db schema.

Shamrox

Grouped by l.custnumber and still no good.

What's the best way to show you my db schema?

Shamrox

Perhaps should I be using a JOIN for the custnumber field instead of the WHERE statement??