Reputation: 18198
Let's say I have this MySQL table:
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:
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
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
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