Query help needed 2

[VAMick]

I am using the below query and for some reason, when I add in the pubid it drops records from my output list. Maybe someone can tell me how it's written wrong.

SELECT c.custnumber, c.custtype, c.firstname, c.middleinitial, c.lastname, c.companyname, c.department, c.region, 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 l.custnumber

The above runs fine, outputting all the desired records, when I add l.pubid right before the MAX(l.quantity) there are records being dropped.

What my desired output is, one record for each customer, the max quantity for any pub they desire (that is in the "display" list, and then also i need to see the pubid for the one that is selected as the max quantity.

ok, hopefully that wasn't to confusing. thanks for the help.

 

[TonyGroves]

You're using the wrong logic there. You would need something like:
[tt]
SELECT
c.custnumber, c.custtype, c.firstname, c.middleinitial, c.lastname,
c.companyname, c.department, c.region, c.street1, c.street2, c.city, c.state,
c.zipcode, c.zip4, c.country, c.countrycode, l2.pubid, l1.maxqty
FROM
(
SELECT custnumber,MAX(quantity) maxqty
FROM moms_lists
WHERE pubid IN (".$_POST['display'].")
GROUP BY custnumber
)
l1
JOIN moms_lists l2
ON l1.customer=l2.customer AND l1.maxqty=l2.quantity
JOIN moms_customer c
ON l1.customer=c.customer
[/tt]

 

[VAMick]

Tony,
I'm getting lost in the l1 l2 logic. Also, where is the field customer coming from?

 

[TonyGroves]

Sorry, "customer" should be "custnumber".

l1 contains the maximum quantity (maxqty) for each customer. That is then joined to moms_lists to get the pubid corresponding to each maxqty. Then moms_customer is joined to get all the customer details.

 

[VAMick]

I figured it was supposed to be custnumber, but when I changed it and ran the query it just hangs and times out.

Any thoughts?

Thanks for taking the time to explain as well!

 

[TonyGroves]

If moms_lists is a big table, then it could take a while. Have you an index on custnumber?

 

[VAMick]

Ok, i got it to run after putting an index on that field, BUT it gave me crazy output.

It's giving me the customer multiple times and also for pubid's that weren't listed in the "display" parameter. perhaps a problem with the the join of quantity instead of pubid??

 

[TonyGroves]

OK. I can see why it might produce two (or more) records for a customer - if the customer has more than one record with the same maxqty value. A way to overcome this would be to add the clause "GROUP BY custnumber" at the end of the query; this (MySQL facility) will list one of the records for the customer.

Then, regarding the listing of pubids that were not in the "display" list, you could add "AND pubid IN (...)" (suitably edited) to the first ON condition.

If that still doesn't produce the right results, you could try eliminating the two joins and associated fields and check that what's produced is correct; if so, then reinstate the first join and associated fields, checking its output.

 

[VAMick]

Tony, just wanted to say thanks for all the help/info you have provided. After some minor tweaking I got the query to work correctly and learned some new stuff along the way.

Thanks.

Below is the resulting query for fyi...

SELECT DISTINCT
  c.custnumber, c.custtype, c.firstname, c.middleinitial, c.lastname,
  c.companyname, c.department, c.region, c.street1, c.street2, c.city, c.state,
  c.zipcode, c.zip4, c.country, c.countrycode, l1.pubid, l1.maxqty
FROM
  (
    SELECT custnumber, pubid, MAX(quantity) maxqty
    FROM moms_lists
    WHERE pubid IN (".$_POST['display'].")
    GROUP BY custnumber
  )
    l1
  JOIN moms_lists l2
    ON l1.custnumber=l2.custnumber
  JOIN moms_customer c
    ON l1.custnumber=c.custnumber