How to select records with associated IDs but exclude them on the result?

Question

I'm encountering a quite confusing logic right now to query some sort of records. Now, I have a table that have various records associated with their ProductID and UserEmail. I need an exact sql statement to produce the results I need. What I want to do here is to query or fetch sets of records that have ProductIDs associated with the ProductID of that UserEmail and exclude that record with having that particular UserEmail. See for example, I have a table below and I want to fetch all sets of records with ProductIDs that have a UserEmail = myemail@myemail.com and exclude that particular record that has that UserEmail. I tried some statement using join and I ended up of using this statement but would still return null values.

 SELECT DISTINCT ProductID,UserEmail,Comments,UserName FROM [ProdCommentsTab]
    WHERE NOT EXISTS( SELECT ProductID,UserEmail,Comments,UserName FROM [ProdCommentsTab]
    WHERE  UserEmail = @useremail)

Table

 **ProductID**      **UserEmail**     **UserName**         **Comments**
   c764cbc3      ddgdfh43@yahoo.com    dfgfdhfdhfg     fgshhfdfgh fhdfhhfgj 
   c764cbc3      myemail@myemail.com      MyName      dgsfdhfg fghdfjghj   
   c764cbc3      mitmak84@outlook.com  dgdfgfhfhf   dgsdgdf fghfdhfg 
   f08b9787      dgsdhf23@gmail.com    dfgsdffhhf   dfgsdhf fhfhfhf fhfhfh
   f08b9787      dgsdfgf67@yahoo.com   dgsdgdfhfgh   dfgshf fhdfhg
   f08b9787      myemail@myemail.com     MyName     sdsdgdsgf dfhfhfdg
   f08b9787      sdgsdhf@outlook.com   dgsdgfhfdg    dgsdfhffh fghdjghj
   b1d9dd41      dfsfhfgh45@gmail.com  dsgdfgd       sdgdsgfd fhfdhfg 
   b1d9dd41      myemail@myemail.com      MyName     dgsdhfdg fghdjgj
   e4f9cvd21     sdfgdfdf@yahoo.com    dgsdfhfdfg    dfgshfg fggjgh fghgjg
   e4f9cvd21     sdfdgdf@gmail.com     dfgdshfhf     dfgdhfg fghdfggjg 

Output Result should look like this

 **ProductID**      **UserEmail**     **UserName**         **Comments**
   c764cbc3      ddgdfh43@yahoo.com    dfgfdhfdhfg     fgshhfdfgh fhdfhhfgj      
   c764cbc3      mitmak84@outlook.com  dgdfgfhfhf      dgsdgdf fghfdhfg 
   f08b9787      dgsdhf23@gmail.com    dfgsdffhhf      dfgsdhf fhfhfhf fhfhfh
   f08b9787      dgsdfgf67@yahoo.com   dgsdgdfhfgh     dfgshf fhdfhg     
   f08b9787      sdgsdhf@outlook.com   dgsdgfhfdg      dgsdfhffh fghdjghj
   b1d9dd41      dfsfhfgh45@gmail.com  dsgdfgd         sdgdsgfd fhfdhfg 

Answer 1

First of all an EXISTS or NOT EXISTS clause should be correlating, i.e. you should relate the records therin with your outer query. (Or use IN to have it non-correlated.) You want EXISTS here, because you are looking for products for which that email exists.

Then you must exclude the record with the email itself in your WHERE clause.

DISTINCT should not at all be necessary here. Why would you have duplicate records in the table?

So:

select productid, useremail, comments, username 
from prodcommentstab this
where exists
( 
  select *
  from prodcommentstab other
  where  other.useremail = @useremail
  and other.productid = this.productid
)
and useremail <> @useremail;

SQL fiddle: http://www.sqlfiddle.com/#!3/f2644/2

Or with IN:

select productid, useremail, comments, username 
from prodcommentstab
where productid in
( 
  select productid
  from prodcommentstab
  where useremail = @useremail
)
and useremail <> @useremail;

SQL fiddle: http://www.sqlfiddle.com/#!3/f2644/3

Now that your question is no longer tagged MySQL, here is an alternative where you read the table just once. It uses an analytic function to check whether a product has an entry for the email in question. As analytic functions cannot be used in the WHERE cause, we need an outer query to filter the results.

select productid, useremail, comments, username
from
(
  select productid, useremail, comments, username,
   max(case when useremail = @useremail then 1 else 0 end) 
     over (partition by productid) as has_useremail
  from prodcommentstab
) mydata
where has_useremail = 1
and useremail <> @useremail;

SQL fiddle: http://www.sqlfiddle.com/#!3/f2644/4

Often a query that scans the table just once to gather all data is much faster, so you could give it a try.

Answer 2

Use Self Join. First Find all rows related to your input email, once you find this data, join it with same table on same productids but different emails ids. Try Using This:

select p1.ProductID,p2.UserEmail,p2.UserName,p2.Comments
from ProdCommentsTab p1
inner join ProdCommentsTab p2 on p1.ProductID = p2.ProductID and p2.UserEmail != p1.UserEmail
where p1.UserEmail = 'myemail@myemail.com'