How to get a selection from tables related using a many-to-many relationship

userEP4k1 0
Viewed 20

I'm new to sql, I've looked at similar questions and answers on the platform, but still couldn't solve my problem.

I have three tables:

CREATE TABLE CartProducts (
    Id        PRIMARY KEY
    ProductId int
    Quanity   int
)

CREATE TABLE  CartCartProducts ( /* <-- many to many table */
    CartProductsId PrimaryKey
    CartId         PrimaryKey
)

CREATE TABLE Cart (
    Id     PrimaryKey
    UserId int
)

Before the query I have the following data:

  • I know the UserId
  • I know the ProductId
  • I know the CartId

I need to get the user's product with the passed product's id and user's id

I tried this:

SELECT 
    * 
FROM
    cart_products cp
    JOIN cart_cart_products cm2m1 ON cm2m1.cart_id = %d 
    INNER JOIN cart_cart_products cm2m2 ON cm2m2.cart_products_id = cp.id

I know that using * is not recommended, I will remake it in the future

sql postgresql
1 Answers

you always have to join all tables. with their columns, after that you have access to all columns and can extract all information needed.

the use of placehoder and parpameters, i omitted, as i don't know which langua you are using, but a quick search should find you a lot of answers

Following query fund you the quantity of a cart with number 1 of userid 1 and of the product 1

Fromhere you have all possibility, to search for all cart of a user, or all products he purchased

SELECT 
    cp.Quanity
FROM
    cart_products cp
    JOIN cart_cart_products cm2m1 ON cm2m1.cart_id = cp.iId
    INNER JOIN cart c = c.Id = cm2m1.CartId
WHERE c.UserId  = 1 AND c.id = 1 AND cp.ProductId = 1
nbk0
Related