php - Null result while use and operator in MYSQL IN

971

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 ?

730

Answer

Solution:

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
627

Answer

Solution:

If yourcountry_id andskill_id areint 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
                 )
373

Answer

Solution:

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')

People are also looking for solutions to the problem: php - Sort or order items with multiple relationships in laravel 5.1

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.