CODEWITHSUNDEEP
sqlpostgresql
MegaRoks
MegaRoks

Reputation: 948

A query that counts the number of records from another table

I have tables merchants:

merchant_id |  merchant_name  | ... |
-------------------------------------
      1     | merchant name 1 | ... |
      2     | merchant name 2 | ... |
      3     | merchant name 3 | ... |

Product Table products:

 product_id |  product_name  | ... |
------------------------------------
      1     | product name 1 | ... |
      2     | product name 2 | ... |
      3     | product name 3 | ... |

And there are table likes_products:

 like_id |  product_Id  | user_id |
-----------------------------------
    1    |      101     |   101   |
    2    |      102     |   102   |
    3    |      103     |   101   |

Table likes_merchants:

 like_id |  merchants_Id  | user_id |
-----------------------------------
    1    |       107      |   101   |
    2    |       108      |   102   |
    3    |       109      |   101   |

Table bookmarks_products:

 bookmark_id |  product_Id  | user_id |
---------------------------------------
       1     |      101     |   101   |
       2     |      102     |   102   |
       3     |      103     |   101   |

I make a request that displays all the products, counts their number of likes. Then he looks like the user from the user who is now authorized products, and if this user has the product tabs, and displays true or false:

SELECT P.* , 
  COUNT(L.USER_ID) AS LIKES,
  (B.PRODUCT_ID IS NOT NULL) AS BOOKMARKS,
  (L.PRODUCT_ID IS NOT NULL) AS IS_LIKED
    FROM PRODUCTS AS P
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID)
        GROUP BY P.PRODUCT_ID, B.PRODUCT_ID, L.PRODUCT_ID
          ORDER BY P.PRODUCT_ID

My problem is that there: 1) I can’t check what an authorized user likes or likes in bookmarks. If any caller likes a product or bookmarks it, he will release true.

2) I can not understand why he considers the number of likes and bookmarks together. That is: if a user has put a like and bookmarked a product, then one like is put, then another user has bookmarked this product, then the likes have increased, and if he likes this product, they will be doubled.

Help me understand my problem please

What I expect to receive:

 product_id |  product_name  | ... | LIKES | BOOKMARKS | IS_LIKED |
-------------------------------------------------------------------
      1     | product name 1 | ... |   1   |   false   |   true   |
      2     | product name 2 | ... |   0   |   true    |   true   |
      3     | product name 3 | ... |   5   |   false   |   false  |

But the problem is that if there are likes from users in the table, there are bookmarks from users in the table, it sums them up and outputs the correct half-half likes.

This is what happens without GROUP BY:

SELECT P.* , 
  (B.PRODUCT_ID IS NOT NULL) AS BOOKMARKS,
  (L.PRODUCT_ID IS NOT NULL) AS IS_LIKED
    FROM PRODUCTS AS P
      LEFT JOIN BOOKMARKS_PRODUCTS AS B ON (B.PRODUCT_ID = P.PRODUCT_ID)
      LEFT JOIN LIKES_PRODUCTS AS L ON (L.PRODUCT_ID = P.PRODUCT_ID)
          ORDER BY P.PRODUCT_ID

 product_id |  product_name  | ... | LIKES | BOOKMARKS | IS_LIKED |
-------------------------------------------------------------------
      1     | product name 1 | ... |   1   |   false   |   true   |
      1     | product name 1 | ... |   1   |   false   |   true   |
      3     | product name 3 | ... |   5   |   false   |   false  |

Product 1 is entered twice as it is in two tables likes_products and bookmarks_products

Upvotes: 1

Views: 76

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below -

select A.product_id,likes,
case when B.PRODUCT_ID IS NOT NULL then 'true' else 'false' end AS BOOKMARKS,
case when L.PRODUCT_ID IS NOT NULL then 'true' else 'false' end AS IS_LIKED
from
(  
SELECT P.PRODUCT_ID , 
  COUNT(L.USER_ID) AS LIKES,
  FROM PRODUCTS AS P
  LEFT JOIN LIKES_PRODUCTS AS L ON L.PRODUCT_ID = P.PRODUCT_ID
  GROUP BY P.PRODUCT_ID
)A 
LEFT JOIN BOOKMARKS_PRODUCTS AS B ON B.PRODUCT_ID = A.PRODUCT_ID
LEFT JOIN LIKES_PRODUCTS AS L ON L.PRODUCT_ID = A.PRODUCT_ID
order by A.product_id

Upvotes: 1

Related Questions