How to write this query?

Question

0.00/5 (No votes)

See more:

Hi,
I have a table Named ProductLike(ProductId, UserId, Like). Whenever user likes a product then a record is added in this table with UserId, ProductId(which product is liked) and Like(true or false).
I would like to get such usersId from the ProductLike table who has liked one product. To be more precise, I want to get a list or group of users who has liked same product for each product.

Please guide How can I write a query.

I am writing this one but it has errors:

SQL

select p.UserId,* from ProductLike p where p.ProductId = (select pk.ProductId from ProductLike pk
where pk.ProductLike=1)

Please guide as my query is not a solution it is just hint.

Posted 11-Jan-13 9:09am

touseef4pk

Updated 11-Jan-13 9:30am

v5

Add a Solution

Comments

Wendelius 11-Jan-13 15:15pm

Can you write a small example of the data and the desired output?

touseef4pk 11-Jan-13 15:36pm

ProductId UserId Like
1 2 True
2 3 True
1 4 True
1 1 False

2 2 True
This is my my example data. Now for example productid 1 is liked by two users. I want that list/group of users for every product

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Answer 1 · 2013-01-11T09:41:00

Solution 2

SQL

SELECT ProductId, UserId
FROM ProductLike
WHERE [Like] = 1
ORDER BY ProductId, UserId

If you want a list for a specific product (for example ProductId 1) add

SQL

AND ProductId = 1

to the WHERE clause.

Please note that Like is a reserved keyword[^] and you might want to use another name for that field.

Posted 11-Jan-13 9:41am

André Kraak

Updated 11-Jan-13 9:45am

v4

Comments

Maciej Los 11-Jan-13 17:17pm

Complete answer, +5!

Wendelius · Answer 2 · 2013-01-11T10:01:00

Solution 3

Not sure if I understood the question correctly, but you mentioned group. So if you need each product and how many users liked it, the query could be something like:

SQL

SELECT pl.ProductId,
       COUNT(*) AS NumberOfLikes
FROM   ProductLike pl
WHERE pl.[Like] = True
GROUP BY pl.ProductId

Posted 11-Jan-13 10:01am

Wendelius

Comments

Maciej Los 11-Jan-13 17:18pm

My favorite answer! +5!

fjdiewornncalwe · Answer 3 · 2013-01-11T09:40:00

Solution 1

Not sure if I'm missing somethin in your question, but I would think this would do the job. You'll get a result set that contains all the products in sections.

SQL

SELECT ProductId, UserId
  FROM ProductLike
 WHERE Like = 1
 ORDER BY ProductId, UserId

Posted 11-Jan-13 9:40am

fjdiewornncalwe

Updated 11-Jan-13 9:42am

v2

Comments

Maciej Los 11-Jan-13 17:17pm

I prefer the 3. solution ;)