CODEWITHSUNDEEP
sqlpostgresql
Casper
Casper

Reputation: 313

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

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 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

Upvotes: 1

Views: 71

Answers (1)

nbk
nbk

Reputation: 49395

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

Upvotes: 1

Related Questions