CODEWITHSUNDEEP
phpmysql
user5848264
user5848264

Reputation:

Null result while use and operator in MYSQL IN

This is my query to get filter data from user table where category will fetch from another table.

SELECT * FROM jobfia_users 
WHERE country_id='4' 
  and user_id IN (SELECT worker_id 
                  FROM jobfia_worker_skills 
                  WHERE skill_id = '42'
                 )

This is not giving any error, but not return any row also. while there are lots of records are available in table using this filter.

Can any one help please ?

Upvotes: 0

Views: 50

Answers (3)

Solmyr
Solmyr

Reputation: 84

Tested your code with and without '' and it works. Make sure you have data and you did not misspell some column name.

Maybe you have collision of some column names. Try to use this syntax:

\`table_name\`.\`column_name\`

Code:

SELECT *  
FROM `jobfia_users` 
WHERE `jobfia_users`.`country_id`='4' 
  AND `jobfia_users`.`user_id` IN (SELECT `jobfia_worker_skills`.`worker_id` 
                                   FROM `jobfia_worker_skills` 
                                   WHERE `jobfia_worker_skills`.`skill_id` = '42')

Upvotes: 0

Thomas G
Thomas G

Reputation: 10216

Additionally to the quotes surrounding your INT ids, your query will be better expressed like this :

SELECT u.* 
FROM jobfia_users  u 
INNER JOIN jobfia_worker_skills ws 
        ON ws.worker_id=u.user_id AND ws.skill_id = 42
WHERE u.country_id=4

Upvotes: 1

Thomas Rollet
Thomas Rollet

Reputation: 1569

If your country_id and skill_id are int type, remove ' around values.

SELECT * FROM jobfia_users 
WHERE country_id=4
  and user_id IN (SELECT worker_id 
                  FROM jobfia_worker_skills 
                  WHERE skill_id = 42
                 )

Upvotes: 0

Related Questions