[RESOLVED] Query assistance needed

Shamrox

First the tables

tbl_customer

custnumber
firstname
lastname
street
city
state
zip

tbl_lists

custnumber
pubid
quantity

I need to create a mailing list of names from the customer table based on a query that has a list of pubid's.
The list should only show a person's name once, but also only give the quantity that is greatest for any pubid. So for example John Smith has an enter for pubid 4, 5, and 6 with a quantity of 1, 2 and 3 respectively. If my query asked for pubid 4, 5 and 6, it would give me John's name once, and a quantity of 3.

I think I use the unique command for the name and max for quantity. Is this right?? Help would be much appreciated.

cornercuttinjoe

im not quite sure what you are asking. are you wanting to output the quantity in the tbl_lists table or are you wanting to output the number of occurances that a custnumber occurs in the tbl_lists table?

if you just want the max quantity, then sure, just use SELECT DISTINCT custname, MAX(quantity) FROM tbl_lists WHERE custnumber = some (SELECT custnumber FROM tbl_customer) or something like that, and that is assuming you want it for everyone all at once. its hard for me to say. im not quite sure what you are asking for.

Shamrox

I need to output a list of names and addresses along with the quantity that match the pubid's. It's to create a mailing list. But the list should only list a person's name once, even though they may be associated with several of the pubid's that are in the query string. And for the quantity, i need the amount that is greatest for that person of all the pubid's listed.

I hope that's not to confusing.

cornercuttinjoe

hmm, i think might get it.

try this...

SELECT DISTINCT firstname, lastname, street, MAX(quantity) FROM tbl_customer, tbl_lists WHERE tbl_customer.custnumber = tbl_lists.custnumber WHERE pubid = '$mypubid'

im still kind of confused on what you are wanting from the pubid. but i imagine your query will be something like this. it may help if you show the query you have tried, so we can see what is running through your mind.

so is your query based upon a given pubid, and then you base the query off of that, or are you wanting to know this for all pubid's? what parameters does the query receive?

Shamrox

The query will be created on the fly via a webpage. The user has a list of pubid's that will create the list. They will select 1 or more pubid's from a list and create the query, upon submitting, it will take that string of pubid's and create the mailing list.

A person in the customer table, could be listed for many pubid's....but the output list should only show their name once and at the same time, grab the max quantity they have requested.

cornercuttinjoe

ah, ok, that makes a little more sense now.

so pretty much what i wrote earlier seems about right, except i had two WHERE's.

SELECT DISTINCT firstname, lastname, street, MAX(quantity) FROM tbl_customer, tbl_lists 
WHERE tbl_customer.custnumber = tbl_lists.custnumber AND pubid = '$mypubid'

as far as i can tell, this is something like you want. post what happens when you try it

Shamrox

That looks perfect. Question, when using the DISTINCT, does it only apply to the field you put it in front of ie. in your code, the firstname field? So if I wanted a distinct first and last name, would i use it twice?