SQLTeam.com | Weblogs | Forums

Help with Query


#1

Hi,

It's hard for me to think of an appropriate title to give this post, otherwise I would provide one more descriptive. So sorry for that.

I have data in a table like this:

USER ID
smith_john_email 111222
smith_john_pager 111222
doe_jane_pager 222333

And so forth. Some ID's have a record entry ending in "_email". I need to get a listing of all distinct ID's which do not have an entry like that. I'm not sure how to do this.

Would greatly appreciate your help. Thank you.


#2

That is two different columns above: USER and ID. With data underneath.


#3
  1. please provide an example of the problem (I do not see "e_mail" in your data)
  2. It would be nice to have example data in consumable form (INSERT INTO)
  3. What have you tried?

#4

Oh sorry. I meant to write _email.


#5

Look at:

SELECT User, ID 
FROM mytable
WHERE USER NOT LIKE '%[_]email';

#6
SELECT ID
FROM table_name
GROUP BY ID
HAVING MAX(CASE WHEN USER LIKE '%[_]email' THEN 1 ELSE 0 END) = 0
--ORDER BY ID