CODEWITHSUNDEEP
mysql
Ronald Fernandes
Ronald Fernandes

Reputation: 285

MySQL query to search for all records against field with comma separated values

I have 2 sql tables Table name: agents contains a records with a coloumn AgentID

Table named: vacancies is the one with the data ans is being dislayed. Table named vacancies has vacancies.Agents which contains values simmilar to this

VacanyID    Company       position        CTC        Candidates        Agents
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FBVAC001  | HDFC      |  Branch Manager | 4.5  | FBCAN001,FBCAN002| Agent3,Agent4
FBVAC003  | TBNH      |  Branch Manager | 4.5  | FBCAN004,FBCAN005| Agent2,Agent4
FBVAC005  | MMNT      |  Branch Manager | 4.5  | FBCAN008,FBCAN006| Agent3
FBVAC008  | LCFC      |  Branch Manager | 4.5  | FBCAN009,FBCAN023| Agent3,Agent4
FBVAC008  | KOTC      |  Branch Manager | 4.5  | FBCAN009,FBCAN023| Agent5,Agent4

I want to run a query that will return only those records that contain the value that corresponds to agents.AgentID from table name agents. This is the query so far but all it returs are those records that do not have more than one value in vacancies.Agents

for example if the value being searched for is Agent3 it should return rows1,3 and 4 instead it only returns row 3.

SELECT
vacancies.VacancyID,
vacancies.Company,
vacancies.`Position`,
vacancies.CTC,
vacancies.Candidates,
vacancies.Agents
FROM vacancies
, agents
WHERE (FIND_IN_SET(vacancies.Agents,agents.AgentID) <> 0)

How can this be resolved?

Upvotes: 4

Views: 963

Answers (2)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT
  vacancies.VacancyID,
  vacancies.Company,
  vacancies.`Position`,
  vacancies.CTC,
  vacancies.Candidates,
  vacancies.Agents
FROM vacancies,
  agents
WHERE (select
     agents.agentid,
     vacancies.agentid
   from agents
     left join vacancies
       on vacancies.agentid = agents.agentid)
and agents.agentid = 'Agent3'

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

I believe you have your parameters backwards in FIND_IN_SET. The set should come second

FIND_IN_SET(agents.AgentID, vacancies.Agents)

More Info: http://www.bitbybit.dk/carsten/blog/?p=162

Also, if you are wanting to see only a specific agent, you need to filter for that as well, otherwise you're getting every possible combination of agent and matching vacancies (hence the duplicate rows):

AND Agents.AgentID = 'Agent3'

Demo: http://www.sqlfiddle.com/#!2/b4dcb/3

Upvotes: 3

Related Questions