CODEWITHSUNDEEP
mysqlsql
wiwa1978
wiwa1978

Reputation: 2687

SQL query for overview of purchased products per user

I have three tables: User, Product and Purchase

User: id, email
Product: id, name
Purchase: user_id, product_id, transaction_id

The idea is that I can find out for any given user which products he has purchased so that (in my view) I can loop over all the products and indicate which ones have been purchased already by that user. For the purchased products, I would present 'watch this product', for all other I want to display 'purchase this product'.

Right now I'm using the following query, this is to retrieve the list of products for a given user

select p1.product_id as id, p2.name, p2.price, u1.id as user_id, p1.stripe_transaction_id 
from users u1
right join purchases p1 on u1.id = p1.user_id and u1.id = <user_id>
right join products p2 on p1.product_id = p2.id

Based on this, I get the following:

+------+---------------------+-------+---------+-----------------------+
| id   | name                | price | user_id | stripe_transaction_id |
+------+---------------------+-------+---------+-----------------------+
|  100 | Product 1           |  1999 |       3 | _jbshvScW_8961        |
|  100 | Product 1           |  1999 |    NULL | _zrtdXU_6811          |
|  101 | Product 2           |  1999 |       3 | _zvgvKS_2536          |
|  102 | Product 3           |  1999 |    NULL | _asgvMP_6811          |
|  103 | Bundle all products |  4999 |    NULL | _bffgMXX_6811         |
+------+---------------------+-------+---------+-----------------------+

The problem with this query is that it is giving me back multiple entries for product_id (if more people have purchased that product is will display a line with product_id and user_id is NULL). In this particular example, another user has also purchased product with id 100.

Ideally I get to the following (per user):

+------+---------------------+-------+-----------+-----------------------+
| id   | name                | price | purchased | stripe_transaction_id |
+------+---------------------+-------+-----------+-----------------------+
|  100 | Product 1           |  1999 |    false  | _zrtdXU_6811          |
|  101 | Product 2           |  1999 |    true   | _zvgvKS_2536          |
|  102 | Product 3           |  1999 |    false  | _asgvMP_6811          |
|  103 | Bundle all products |  4999 |    false  | _bffgMXX_6811         |
+------+---------------------+-------+-----------+-----------------------+

Been trying already a while but not getting there. Any help is appreciated!

Upvotes: 1

Views: 1928

Answers (3)

Sneha
Sneha

Reputation: 39

select prod.product_id as id, prod.product_name as name, prod.price as price,
case when u.user_id is null then 'false' else 'true' end as pruchased,p.transcation_id
from products prod left join purchases p on p.product_id=prod.product_id 
left join users u on u.user_id = p.user_id and u.user_id=101;

Upvotes: 0

ScottieB
ScottieB

Reputation: 4052

As I understand, you want to input a user_id and get an output of every product they could have purchased, and if they did.

If that's so, you could CROSS JOIN users to products, then fill-in purchase data from purchases. But CROSS JOINs are dangerous and easy to muck up, so I avoid.

Instead, why not just use product data and fill in with purchase data?

SELECT
  p.id as product_id
  ,p.name
  ,p.price
  ,r.stripe_data
FROM products p
LEFT JOIN purchases r ON p.id = r.product_id AND r.user_id = <your user>

This assumes a user can only purchase a product once though.

Upvotes: 2

Kh.Taheri
Kh.Taheri

Reputation: 957

You can use a nested query, rather than the right join, like the following:

select * from products where product_id in (select product_id from purchase where user_id = <user_id>)

You can also customize the resulted view as you like to get the prices and the other data.

Upvotes: 0

Related Questions