Return row if different multiple id matches with different column

Question

I have single table name as job_post.Now int this table city stored with the multiple city id and skill stored with the multiple skill id as show in below table.

I want to search using multiple skill id like 1,2 and multiple city id in this table.

I have tried query like this :

select * from job_post where FIND_IN_SET(city, '1,3') or FIND_IN_SET(skill, '1,3')

So, I got one result which have city 1 but I want both row instead of it if it matches single id from skill or city column.

So what query should I have to write?

Possible duplicate of [MySQL find\_in\_set with multiple search string](http://stackoverflow.com/questions/5015403/mysql-find-in-set-with-multiple-search-string) — alex9311, Apr 11 '16 at 13:07
I want to return both row if you find in skill column as **1,3** and city column as **1,3**.Because **3** id is in skill column and **1** id is in city column. — Nisarg Bhavsar, Apr 11 '16 at 13:13

score 0 · Answer 1 · edited May 23 '17 at 12:25

This is a needlessly complicated query, you really should use foreign keys and additional tables to set this up

But if you want to keep the same schema you can use a hack. Check this post for some examples. I use the following to implement it

Create Table job_post (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  skill VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO job_post (name,skill,city) 
  VALUES ('job1','2,3','1,2'),
  ('job2','2,3,12,13','1');   

SELECT * FROM job_post WHERE CONCAT(',', skill, ',') REGEXP ',(1|3),'
  OR CONCAT(',', city, ',') REGEXP ',(1|3),';

So if you're executing these from php, you just need to change the regex to include as many ids as you want. The above query returns both rows.

This query

SELECT * FROM job_post WHERE CONCAT(',', skill, ',') REGEXP ',(1),' OR CONCAT(',', city, ',') REGEXP ',(2|3),';

Only returns job1. You can play with the example here

Return row if different multiple id matches with different column

1 Answers1