CODEWITHSUNDEEP
mysqlsqlconditional-statements
MyFault
MyFault

Reputation: 427

MySQL SELECT email where field contains a value

I would like to do a simple select - but the condition is a bit tricky for me (because I'm a SQL-beginner).

I got this table:

userid | email             | newsletters
     1 | [email protected]  | 1,2
     2 | [email protected] | 1

Now I would like to get all email-addresses of users, which want to get newsletter "2".

This would be:

email | newsletters
[email protected] | 1,2

And of course: In another query all users, which are subscribing newsletter number 1:

Result:

email | newsletters
[email protected] | 1,2
[email protected] | 1

What would be the correct sql-query? I think this should be the right beginning, but I don't know which condition I have to use:

SELECT email FROM users WHERE newsletter CONDITION?

Could you please help me out? :-)

Upvotes: 1

Views: 3278

Answers (2)

Houkes
Houkes

Reputation: 54

Use a regular expression if you really want to do this, but I think you need to redesign your table structure. Instead of storing the newsletters per user in the User table, you should create a bridge table between User and Newspaper like this:

User table
userid | email            
     1 | [email protected] 
     2 | [email protected]

Newspaper table
paperid | name
      1 | the Sun
      2 | the Mirror

UserNewspaper Bridge table
userid | paperid     (represents, not part of table)
     1 | 1           ([email protected] receives the Sun)
     1 | 2           ([email protected] receives the Mirror)
     2 | 1           ([email protected] receives the Sun)

To get all email-addresses of users that want paperid 2 you'd write this:

select a.email
from   User a,
       UserNewspaper b
where  a.userid = b.userid
and    b.paperid = 2

To get all email-addresses of users that want the Mirror you'd write this:

select a.email
from   User a,
       UserNewspaper b,
       Newspaper c
where  a.userid = b.userid
and    b.paperid = c.paperid
and    c.name = 'the Mirror'

Upvotes: 0

Michal Kordas
Michal Kordas

Reputation: 10925

This will do the work assuming number of newsletter can't be higher than 9:

SELECT email FROM users WHERE newsletters LIKE '%2%'

If you'd like to have more of them then table normalization would be very helpful.

EDIT: @sgeddes in comments has great proposition to make it working for any number of newsletters:

SELECT email FROM users WHERE concat(',',newsletters,',') LIKE '%,2,%'

Upvotes: 2

Related Questions