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

`SELECT * FROM ProdCommentsTab WHERE UserEmail <> @useremail` — Felix Pamittan, Oct 16 '15 at 06:52
Are you really using two different DBMS (MySQL *and* SQL Server) to solve this? One should be enough :-) Please change your tags. — Thorsten Kettner, Oct 16 '15 at 06:57
There is a problem in the example sql query you show us, it is saying "select ... from..." if not exists row result in "SELECT ... FROM [ProdCommentsTab] WHERE UserEmail = @useremail". I think it's not the problem you explained. Where is set @useremail ? — Nico, Oct 16 '15 at 07:19
@Nico: Of course the query is wrong. This is why timmack is asking for help in the first place. The query checks whether the email is in the table, and as it is it returns no rows. And this is not at all what timmack is after. — Thorsten Kettner, Oct 16 '15 at 07:23
@Nico I think you are obviously confused or even didn't understand the logic at all just what I was for 24 hours but don't bother I already resolved it with the help of some guy. I posted an answer to this below. Check them it may help you in the future if you encounter something like this. — timmack, Oct 16 '15 at 08:52
You haven't answered what DBMS you are using. With SQL Server you have options that you don't have with MySQL. — Thorsten Kettner, Oct 16 '15 at 08:55
Yes, sorry I'm using MS SQL Server 2008. What option are you talking about? — timmack, Oct 16 '15 at 08:57
@timmack you're right I'm still not sur of what you want to do :) but happy you found the solution — Nico, Oct 16 '15 at 09:49

Thorsten Kettner · Accepted Answer · 2015-10-16T11:53:19.120

2

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.

edited Oct 16 '15 at 11:53

answered Oct 16 '15 at 06:49

Thorsten Kettner

89,309
7
49
73

this does not produce any result. I ended up with runtime error. The other guy's answer was the nearest one but it was deleted. I should've upvoted him too. Thanks for your effort. I'll just upvote your answer. – timmack Oct 16 '15 at 08:44
A runtime error for such simple queries? Then there must be something wrong with your setup. What error do you get? You do have indexes on productid and useremail, don't you? Maybe even a composite index on both columns? These queries, being the straight-forward solutions, should be quite fast. – Thorsten Kettner Oct 16 '15 at 09:05
I got an error something like index is out of bound array something like that but can't recall anymore the exact error message – timmack Oct 16 '15 at 09:08
As to the error: You should google for `SQL-Server "index was outside the bounds of the array"`. There is something wrong with your setup and you should fix it. – Thorsten Kettner Oct 16 '15 at 09:12
I just found this: http://stackoverflow.com/questions/23202414/index-was-outside-the-bounds-of-the-array-microsoft-sqlserver-smo and this: https://support.microsoft.com/en-us/kb/2459027. One or the other may solve your problems. – Thorsten Kettner Oct 16 '15 at 09:15
Ah, sorry, SQL Server needs an alias for such subqueries (derived tables). I've updated my answer. – Thorsten Kettner Oct 16 '15 at 09:19
Ok there's still error on your query for some reason I don't know. I can't figure out why it won't return values on username and comments. That query didn't consider getting the value for ProductID unlike my updated answer below which will give perfect results that I need. Compare my updated answer in your query and see what differences that you determine. – timmack Oct 16 '15 at 09:42
Well, you can add `where productid= @prodid` to the inner query of course, so you only get one product. I don't know what error you get, so I don't know why you are getting it either, naturally. Anyway, I hope you are able to fix your setup. I've added SQL fiddles to show the queries are working. Something is broken in your DBMS setup and you should really fix that. – Thorsten Kettner Oct 16 '15 at 11:54
Yes, you're right. This is the correct one. I overlooked when I was testing. The answer that I posted before was intended for another query that correlates with this question but I just found out today that your solution was the most appropriate one for this type of scenario so I'll marked as check your answer now. Thanks buddy, you did an excellent job I learned a lot from this. – timmack Oct 17 '15 at 02:34

score 0 · Answer 2 · answered Oct 16 '15 at 07:10

0

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'

answered Oct 16 '15 at 07:10

Ankit Kumar

180
9

this will just give duplicate results like results from iterating a loop – timmack Oct 16 '15 at 08:40

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

2 Answers2