CODEWITHSUNDEEP
mysql
test
test

Reputation: 18198

MySQL - Searching a field twice

Let's say I have this MySQL table:

SQL

OK.. see the type field? Type 0 is when a person KNOWS and a language and Type 1 is when a person is LEARNING that language.

and let's say I have some data:

enter image description here

As you can see, member 3 corresponds with uid (user id). User 3 KNOWS Chinese because of the type 0 but where was User 1 is LEARNING Chinese because of the type 1.

And finally, let's say I make a form. It has two select fields filled with an array of language selections. After a certain submit... $lang_learning outputs zh and $language_know outputs en. How would I find a person who speaks zh and is learning en without conflicting with the language field since you're essentially looking for it twice?

Thanks.

Upvotes: 0

Views: 45

Answers (2)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230561

You need a self join. Try this.

SELECT t1.uid 
FROM languages t1
INNER JOIN languages t2 ON t1.uid = t2.uid
WHERE t1.language = 'zh' AND t1.type = 0
  AND t2.language = 'en' AND t2.type = 1;

Upvotes: 3

Marc B
Marc B

Reputation: 360912

You can't do it with a simple query, as standard 'where' clauses consider records row-by-row. Simple queries cannot do "select a record where field X is 'a' in one record and field X is 'b' in another.

For this, you'd need a join:

SELECT knows.uid
FROM yourtable AS knows
INNER JOIN yourtable AS learning ON knows.uid = learning.uid
WHERE (knows.language='zh' AND knows.type=0) AND (learning.language='en' AND knows.type=1)

Upvotes: 0

Related Questions