Not sure how to write the query

[shamrox]

Ok, I've got three tables

Cust Tbl
--------
CustNumber (ID)
Name
Address
City
State

List Tbl
---------
CustNumber (linked to Cust)
PubNo
Quantity

Pub Tbl
--------
PubId
PubNo (linked to List tbl)
Title

Now...the final output i need is a mailing list that would give me the name and the quantity. BUT...I would be selecting multiple PubNo's and I'd need the name to show up once and the max Quantity. (More Info) Each person could be listed for different PubNo...so they could show up many times and they'd have different quantities selected. I need the end result to show their name once, and the highest number of quantity they have picked.

I hope this helps and someone can lead me in the right direction. Thanks.

 

[TonyGroves]

Sorry, it's not very clear what you're trying to do. Can you be a bit more specific?

 

[shamrox]

I need to create a query that would consist of multiple selections of PubNo.'s

The query would return the name and mailing info and the max quantity count.

So, for example let's say John Smith is listed for PubNo 1 with Qty 3, PubNo 4 with Qty 8, and PubNo55 with Qty 12.

If my query was to include all 3 of those PubNo's, his name would show up once with Qty 12.

I hope that helps explain what i need.

 

[r937]

select Cust.CustNumber
     , Name
     , Address
     , City
     , State
     , sum(Quantity) as total_Quantity
  from Cust
inner
  join List
    on Cust.CustNumber = List.CustNumber
 where List.Pubno in ( 1, 4, 55 )
group
    by Cust.CustNumber
     , Name
     , Address
     , City
     , State

r937.com | rudy.ca