Find all customers who are back-versioned

[smeridew]

I need to write an ACCESS query that goes something like this:

For each customer (table customer-product)
For each product they have (table customer-product)
If they don't have the latest product (table product)
Include that customer+product combo in the result

The customer-product table is related to the product table via a productID foreign key.

Sample data:

customer-product table:

Company ProductID Version
=======================+========
ABC Company ProductA Version1
ABC Company ProductA Version2
ABC Company ProductB Version1
DEF Company ProductA Version1
DEF Company ProductB Version1
DEF Company ProductB Version2

product table:

Product Version CurrentVersion?
===================================
ProductA Version1 False
ProductA Version2 True
ProductB Version1 False
ProductB Version2 True

The result of this query should give me:
ABC Company ProductB
DEF Company ProductA

ANY HELP IS APPRECIATED !!!

Scott.

Scott Meridew
Director of Professional Services, TxMQ Inc.
Certified MQSeries Solution Expert
Certified MQSeries Developer
Certified MQSeries Specialist
Microsoft Certitifed Systems Engineer

http://www.txmq.com

 

[fredericofonseca]

Just typed, NOT tested, but should give you an idea.

select a.company, b.productid from
(select company, productid, max(version) as last_version from
customer-product
group by company, productid) as A
left join
(select productid, max(version) as last_version
from product
group by productid) as B
on a.productid = b.productid
and a.last_version = b.last_version

where b.productid isnull

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

A few corrections (tested):
select a.company, a.productid from
(select company, productid, max(version) as last_version from
[customer-product]
group by company, productid) as A
left join
(select product, max(version) as last_version
from product
group by product) as B
on a.productid = b.product
and a.last_version = b.last_version
where b.product is null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Another way:
SELECT Company, ProductID
FROM [customer-product] A
GROUP BY Company, ProductID
HAVING Max(Version)<>(SELECT Version FROM product B
WHERE B.Product = A.ProductID AND [CurrentVersion?] = True)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smeridew]

Wow - thanks so much for this information. I will try it out and let you know how it works. Up til now, I've been managing an 'indicator' flag in the table, but it's a major pain to maintain this flag as customers buy new products.

Very much appreciate you taking the time to answer my question.

Scott.

Scott Meridew
Director of Professional Services, TxMQ Inc.
Certified MQSeries Solution Expert
Certified MQSeries Developer
Certified MQSeries Specialist
Microsoft Certitifed Systems Engineer

http://www.txmq.com

 

[smeridew]

Ok - I made a few mistakes...here are the actual tables, with the pertinent data. CustProd is really like a join between customer and product, to enable a many-to-many relationship. Let's pretend that the product is some microsoft software.

Product Table
=============
ProductID (Text)
Productname (Text) - i.e. Microsoft Access 2003
Category (Text) - i.e. Access
Current (Yes/No) - i.e. Yes
ProductVersion (Number: 0 = most curent version) - i.e 0

CustProd Table
==============
CustomerID
ProductID

Customer Table
==============
CustomerID
CustomerName


Products are broken into Categories (we will call them Cat1, Cat2, etc.). Customers may have 2 or 3 of the products in Cat1, but not the most current version (Current=Yes, or ProductVersion=0).

What I need this query to do is this:

For each product category, tell me which customers (Names) have an old software version in this category. So, for example, tell me all customers who have Access, but not the latest version.


Scott Meridew
Director of Professional Services, TxMQ Inc.
Certified MQSeries Solution Expert
Certified MQSeries Developer
Certified MQSeries Specialist
Microsoft Certitifed Systems Engineer

http://www.txmq.com

 

[PHV]

With your posted schema, I don't see where is the problem ...
SELECT P.*, C.CustomerName
FROM Product P INNER JOIN (
CustProd CP INNER JOIN Customer C ON CP.CustomerID = C.CustomerID
) ON P.ProductID = CP.ProductID
WHERE P.ProductVersion <> 0 OR P.Current <> Yes;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886