Compare rows

[newinphp]

Hi everyone!

I've been looking for a solution, but i just can't find it anywhere...

 

Ok, so the problem is:

i have a database which looks something like this:

 

user_id product_id score

14 . 235 . 79

23 . 235 . 32

53 . 665 . 21

14 . 235 . 90

5 . 675 . 45

 

 

This is step-by-step of what i need to do:

 

1. Select user - user_id =343

 

2. For user_id =343 select all products that he gave score to:

product_id = 43 - score = 99

product_id = 12 - score = 56

product_id = 68 - score = 32

product_id = 124 - score = 67

 

3. Find all users that voted for the same products e.g.:

for product_id = 43:

user_id = 125 voted 93 points,

user_id = 23 voted 56 points.

 

 

4. Calculate the difference between the score of user, so:

for product_id = 43:

user_id = 125 voted 93 points (99 - 93 =6 points of diff.)

user_id = 23 voted 56 points (99 - 56 =43 points of diff.)

return the results....

 

 

That's basically what i need to do. I still don't know the number of the users, or product, but it doesn't really matter - i just need to make it work.

Ok, hope someone can help!

[AyKay47]

what have you come up with so far?

 

Edit: to point you in the right direction, you will most likely be using a self join here.

http://www.thunderstone.com/site/texisman/joining_a_table_to_itself.html

[Psycho]

When doing these types of queries it can be very difficult to create the entire query to start with. The best approach, in my opinion, is to create the query in steps. We can go ahead and jump to step 2 in your requirements above:

 

1. Create the query to get all of the products that the target user has scored

SELECT product_id, score
FROM prod_scores
WHERE ps1.user_id = 1

 

Ok, so we have all the products that the user has scored (and the scores), but we also need all the scoring records by other users for the same products. So, we need to JOIN the record from teh same table where the product ID is the same (but we should exclude those records for the target user). But, sicne we will want some of the same fields we should give the fields dynamic names so we don't get confused with what field is what in the results. NOTE: The ps1 fields are those for the target user and the ps2 fields are those for other users.

SELECT ps1.product_id, ps1.score as user_score,
       ps2.user_id AS other_user, ps2.score AS other_score
FROM prod_scores AS ps1
LEFT JOIN prod_scores AS ps2
  ON ps1.product_id = ps2.product_id
  AND ps2.user_id <> 1
WHERE ps1.user_id = 1

 

That gets us most of the way there. Now, we could calculate the difference while we iterate through the results, but we can do that in the query as well. Also, I assume, you need the product names and the user names - so I have added JOINs for those tables to get that info. NOTE: if you need both the username of the target user and the other users, then you will need to JOIN the user table twice. If you already have the target user's name, then you can remove that one JOIN (and the field fromt eh SELECT). And, we would want to ORDER the results by products to get them in a logic order for processing.

 

SELECT u1.username as user_username,                          -- target user's username
       p.productname,                                         -- product names
       ps1.product_id, ps1.score AS user_score,               -- target user's scores
       u2.username as other_username,                         -- other user's usernames
       ps2.user_id AS other_userid, ps2.score AS other_score, -- other user's scores
       (ps1.score - ps2.score) AS difference                  -- calculated difference

FROM prod_scores AS ps1                                       -- target user's scores
JOIN products AS p ON ps1.product_id = p.id                   -- product names
JOIN users AS u1 ON u1.id = 1                                 -- target users username
LEFT JOIN prod_scores AS ps2                                  -- other user's scores
  ON ps1.product_id = ps2.product_id AND ps2.user_id <> 1
JOIN users AS u2 ON ps2.user_id = u2.id                       -- other user's usernames

WHERE ps1.user_id = 1

ORDER BY ps1.product_id

 

This should get you what you need. I have added comments to specific lines to illustrate the purpose of the line. A couple notes:

 

1. The LEFT JOIN for the self JOIN is needed so that the results will include scores from the target user if no other users have scored the same product.

 

2. The results include fields that are probably only needed for debugging purposes (i.e. to make it easier to "see" what the results are. For example, you may not need the user's names and the user's IDs. Plus, you if all you need is the difference you don't need the target user's score and the other user's score in the results. So, once you have validated the results are what you need you can remove the fields from the SELECT query that you don't need. Plus, if you don't need the product names or the user's names you can remove the respective JOINs

[newinphp]

Thanks guys!

Super, will check it out !!!