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 = [email protected] 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      [email protected]    dfgfdhfdhfg     fgshhfdfgh fhdfhhfgj 
   c764cbc3      [email protected]      MyName      dgsfdhfg fghdfjghj   
   c764cbc3      [email protected]  dgdfgfhfhf   dgsdgdf fghfdhfg 
   f08b9787      [email protected]    dfgsdffhhf   dfgsdhf fhfhfhf fhfhfh
   f08b9787      [email protected]   dgsdgdfhfgh   dfgshf fhdfhg
   f08b9787      [email protected]     MyName     sdsdgdsgf dfhfhfdg
   f08b9787      [email protected]   dgsdgfhfdg    dgsdfhffh fghdjghj
   b1d9dd41      [email protected]  dsgdfgd       sdgdsgfd fhfdhfg 
   b1d9dd41      [email protected]      MyName     dgsdhfdg fghdjgj
   e4f9cvd21     [email protected]    dgsdfhfdfg    dfgshfg fggjgh fghgjg
   e4f9cvd21     [email protected]     dfgdshfhf     dfgdhfg fghdfggjg 

Output Result should look like this

 **ProductID**      **UserEmail**     **UserName**         **Comments**
   c764cbc3      [email protected]    dfgfdhfdhfg     fgshhfdfgh fhdfhhfgj      
   c764cbc3      [email protected]  dgdfgfhfhf      dgsdgdf fghfdhfg 
   f08b9787      [email protected]    dfgsdffhhf      dfgsdhf fhfhfhf fhfhfh
   f08b9787      [email protected]   dgsdgdfhfgh     dfgshf fhdfhg     
   f08b9787      [email protected]   dgsdgfhfdg      dgsdfhffh fghdjghj
   b1d9dd41      [email protected]  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 = '[email protected]'