Mysql enum with where in Clause

Question

i have two tables

tweets(id,user_id,lang); 
//lang is enum (en,fr,ar,ch)

user(id,accepted_lang); 
// accepted_lang is varchar and can vary like en,ar OR ar,fa OR en,fa etc

I want to select all tweets with accepted languages by user.

select * from tweets where lang in (select accepted_lang from user where id=16); 
#return 0 row

If i replace select accepted_lang from user where id=16 with its result fa,ar it will return some of rows

select * from tweets where lang in ('fa','ar');
#return some of rows

So actually my problem is how to convert varchar to enum or table

So what exactly is the problem? What does "not working" mean here? Do you get an error, or the wrong results, or nothing at all? Please give more detail. Also, you mention like in the title, but I don't see any like clause in question. — Spudley
– Spudley, Commented Dec 2, 2013 at 12:58
No, it won't use a LIKE clause. It might use an IN clause (depending on exactly what you're trying to do), but not a LIKE. LIKE makes no sense at all with an enum field. — Spudley
– Spudley, Commented Dec 2, 2013 at 13:00

vogomatix · Accepted Answer · 2013-12-02 13:01:50Z

1

Probably because you haven't mentioned the second table and got user_lang instead of accepted_lang in your query

select * from tweets, user where tweet_lang = accepted_lang and user.id=5

However you probably want to accept all tweets from a specific user

select * from tweets
left join user on tweets.user_id = user.id
where -- your conditions here

edited Dec 2, 2013 at 13:01

answered Dec 2, 2013 at 12:56

vogomatix

5,1352 gold badges27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Wasim A. Over a year ago

please check question again

Wasim A. Over a year ago

i have just modified question, its more clarify now.

vogomatix Over a year ago

Is id a primary (unique) key in the user table? If accepted_lang is an enum, then it can contain only one value; if a string or something else then it may hold multiple values. Please add the table schema to your question

Wasim A. Over a year ago

id is primary, if i replace query select accepted_lang from user where id=16 with its result fa,ar in query select * from tweets where lang in (select accepted_lang from user where id=16); it will work fine.

vogomatix Over a year ago

accepted_lang can only hold ONE of fa, ar as enums only hold single values. That is why you're only getting some results back

|

Code Lღver · Accepted Answer · 2013-12-02 12:58:27Z

0

Your query should be this:

SELECT * FROM tweets, user 
     WHERE tweets.tweet_lang = user.accepted_lang 
           AND user.id=5

As there is no column named user_lang probably this is accepted_lang

answered Dec 2, 2013 at 12:58

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

1 Comment

Wasim A. Over a year ago

just modified my question, please check now.

Collectives™ on Stack Overflow

Mysql enum with where in Clause

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related