[RESOLVED] complex query... :(

[bambo]

Hi,

I have a db as followed:
1. people table- with personID, currentFunds.
2. Product table - with productID, price
3. peoplePurchases table- with productID (FK to products), personID (FK to people).

I need to create the following query:
1. will summarize for each person, and each price level of the products (assuming several product could have same price) how many products he purchased of this price, out of how many products he could purchase with his current funds.

example:
current funds= 24$
purchased:
apple-1$
banana- 12$
mango- 12$

(other product existing: choclate= 10$)

person name | 1$ products | 10$ products | 12$ products
john | 1/24 | 0/2 | 2/2

any ideas?

[CVMichael]

Something like this:

SELECT p.name
    , pr.ProductName
    , pr.Price
    , Count(*) AS Total_Purchases
    , FLOOR(p.currentFunds / pr.Price) AS products_he_could_purchase
FROM People AS p
   INNER JOIN peoplePurchases AS pp ON pp.personID = p.personID
   INNER JOIN Product AS pr ON pr.productID = pp.productID
GROUP BY p.personID, p.name, pr.ProductName, pr.Price

That will return records like this:
john | 1 | 24
john | 0 | 2
john | 2 | 2

If you want the result exactly the way you mentioned ( "john | 1/24 | 0/2 | 2/2" ), then you have to use pivot, and that is a bit complicated, so I will show you that only if you really need like that

[bambo]

thanks for quick reply!

are you sure about this part: "ON p.personID = p.personID" - i get syntax error missing operator for this expression (MS Access 2003).

Thanks,

[CVMichael]

I edited and fixed it...

That's what happens when I'm lazy and I name alias names too short

Oh... you are using Access ! Next time make sure you say that when you start the thread as the syntax of the SQL statement may be different (as in this case)

Then you have to put each JOIN inside it's own brackets, like this:

SELECT p.name
    , pr.ProductName
    , pr.Price
    , Count(*) AS Total_Purchases
    , FIX(p.currentFunds / pr.Price) AS products_he_could_purchase
FROM (People AS p
         INNER JOIN peoplePurchases AS pp ON pp.personID = p.personID)
   INNER JOIN Product AS pr ON pr.productID = pp.productID
GROUP BY p.personID, p.name, pr.ProductName, pr.Price

And I don't think you have FLOOR in Access, so I changed it to FIX

That should work (I think)

[bambo]

also are you sure it will get 0/2 (i mean potential of buying for products that the person didn't purchase at all?)

Thanks,

[bambo]

mm can u please detail what did u fix? as i still get the same error for the current code

Thanks,

[CVMichael]

I just changed from
ON p.personID = p.personID
to
ON pp.personID = p.personID

Maybe you should post the exact structure of your tables if you still get the error

[bambo]

i get paramter asking for pp.personID and for pp.productID (where did u define it in the query?).

[bambo]

when switching to design i view i get error for not being able to represent the join expression pp.personID = p.personID

Thanks,

[CVMichael]

i get paramter asking for pp.personID and for pp.productID (where did u define it in the query?)

This: peoplePurchases AS pp

[bambo]

i used with the full db table/ fields names

[rplcmint]

Why are you using the FIX function? Wouldn't that return an integer value? Doesn't he want his data in the fractional value?

[bambo]

any idea why it relates to both pp.personID and for pp.productID as query parameters (and not recognize their values) ?

[bambo]

thats fine, i want integer values. but why doesn't it work ?

[CVMichael]

You know what... as a test, just run this query, tell if you get an error on it:

SELECT *
FROM People AS p
INNER JOIN interactions AS pp ON pp.peopleID = p.peopleID

PS. Nice avatar rplcmint

[bambo]

also i don't need the product name as column, but rather the price group of the product as column (please mind i refer to both products that have same price as one price range in the example - mango/ banana - both are treated as just one row of products with 12$ price).

[bambo]

the test query also doesn't work- it asks for paramter of pp.peopleID

[CVMichael]

the test query also doesn't work- it asks for paramter of pp.peopleID

ok, then are you sure you have peopleID in interactions table ?

[bambo]

oops! you are right peopleID is called SideB_ID on interactions table...

[bambo]

edited it to be this way, and now only pp.ActualServicesID comes up unknown

[CVMichael]

OK, so I removed ActualServiceName, and changed the peopleID to SideB_ID, is it working now ?

SELECT p.peopleID
  , pr.LoyalityPoints
  , Count(*) AS Total_Purchases
  , FIX(p.currentfunds / pr.LoyalityPoints) AS products_he_could_purchase
FROM (People AS p
INNER JOIN interactions AS pp ON pp.SideB_ID = p.peopleID)
INNER JOIN actual_services AS pr ON pr.ActualServicesID = pp.ActualServicesID
GROUP BY p.peopleID, p.name, pr.LoyalityPoints

[CVMichael]

OK... check if field ActualServicesID is in table interactions

[bambo]

ok great!

any ideas how to group together products that have same price?

also there are products that don't have any price and i would like to disregard these (since the person can buy infinite number of these...)

Thanks,

[CVMichael]

OK, Try this:

SELECT p.peopleID
  , pr.LoyalityPoints
  , Count(*) AS Total_Purchases
  , FIX(p.currentfunds / pr.LoyalityPoints) AS products_he_could_purchase
FROM (People AS p
INNER JOIN interactions AS pp ON pp.SideB_ID = p.peopleID)
INNER JOIN actual_services AS pr ON pr.ActualServicesID = pp.ActualServicesID
WHERE IIF(ISNULL(pr.LoyalityPoints), 0, pr.LoyalityPoints) > 0
GROUP BY p.peopleID, p.name, pr.LoyalityPoints

I added the "where", and removed the ActualServiceName

But you did not tell me how you fixed the "pp.ActualServicesID comes up unknown" problem

[bambo]

mm actually not so great it doesn't seem to display products that haven't been purchased so i don't get ever results for total_purchased=0... which is quite important to achive on this query...

about pp.ActualServicesID - you were right again- it had a different field name on that table...

[CVMichael]

OK, but this:

also there are products that don't have any price and i would like to disregard these

is a contradiction of this:

mm actually not so great it doesn't seem to display products that haven't been purchased so i don't get ever results for total_purchased=0... which is quite important to achive on this query...

So, from your last post:
You want to list all people with ALL products (actual_services), and show "0" (zero) to products that don't have any interactions ?

[bambo]

I want to:
1. disregard completely products with zero price.
2. to list people with all products prices (not products) and to show for each product price how much they purchased, and how much they could purchase.

hope its more clear now, please let me know if not

[bambo]

also if it makes any difference i need to display one person at a time as this query is to be used on the person details form (or i could use this query and to filter it by current personID).

Thanks,

[CVMichael]

In that case, I think it's like this, but I have no way of testing it...

SELECT ps.peopleID
  , ps.LoyalityPoints
  , Count(*) AS Total_Purchases
  , FIX(ps.currentfunds / ps.LoyalityPoints) AS products_he_could_purchase
FROM (
    SELECT p.peopleID, p.currentfunds, a.ActualServicesID, a.LoyalityPoints
    FROM People AS p, actual_services AS a
    WHERE IIF(ISNULL(a.LoyalityPoints), 0, a.LoyalityPoints) > 0
) AS ps
LEFT JOIN interactions AS i ON (i.SideB_ID = ps.peopleID AND i.ActualServicesID = ps.ActualServicesID)
GROUP BY ps.peopleID, ps.name, ps.LoyalityPoints

[bambo]

you tried to execute that does not include the specified expression FIX (ps.currentfunds.LoyalityPoints) as part of an aggregate function

[CVMichael]

Ah...

SELECT ps.peopleID
  , ps.LoyalityPoints
  , Count(*) AS Total_Purchases
  , FIX(ps.currentfunds / ps.LoyalityPoints) AS products_he_could_purchase
FROM (
    SELECT p.peopleID, p.currentfunds, a.ActualServicesID, a.LoyalityPoints
    FROM People AS p, actual_services AS a
    WHERE IIF(ISNULL(a.LoyalityPoints), 0, a.LoyalityPoints) > 0
) AS ps
LEFT JOIN interactions AS i ON (i.SideB_ID = ps.peopleID AND i.ActualServicesID = ps.ActualServicesID)
GROUP BY ps.peopleID, ps.currentfunds, ps.LoyalityPoints

I removed ps.name from GROUP BY since you are not using it in the results, and addded ps.currentfunds (the error)

[CVMichael]

I have to go, I will check your messages in a few hours (or tomorow)

[bambo]

Thanks!