:confused: hi everyone, i dont know how to actually state my problem, but i ll try my best. how can i query my database to select all those rows who has oracle,mysql,php and c. i have multiple rows to store user known language. i want to select only those users who know all the four languages. how shud i write the query ? i have tried

select * from user_skills where skillname IN ('oracle','mysql','php','c');
but that select all the rows with any one match. i want something like,
select * from user_skills where skillname IN('oracle' AND 'mysql' AND 'php' AND 'c');

is it possible to select only those who have knowledge on all the 4 languages ? I wud appreciate your help..thanks in advance..

Actually you almost have it. You need to add a count and a HAVING test -

SELECT *, COUNT(*) AS cnt FROM user_skills WHERE skillname IN('oracle','mysql','php','c') GROUP BY name HAVING cnt = 4So what does this do -

SELECT * - this will return all columns, if you print the results of this query, it will be the first row found in the database for each person's name with the cnt (which will be 4) appended at the end.

COUNT(*) AS cnt - this will create a count of the rows in each GROUP BY and refer to it as the name "cnt"

FROM user_skills WHERE skillname IN('oracle','mysql','php','c') - this is the basic query to get all the rows with 'oracle' OR 'mysql' OR 'php' OR 'c'.

GROUP BY name - this groups the rows in the result so that the COUNT(*) can count the number of rows in each group. This assumes that there is a column called "name".

HAVING cnt = 4 - this limits the results to those HAVING four rows in the result. Note that if there are duplicates (two with PHP as an example - 'oracle','mysql','php','php') it will match and say that this person has all four skills.

Moved to Database.

thanks a lot bro.. ty.. ;)