Dennis Dennis - 2 months ago 6
MySQL Question

MySQL query using different tables and filters

I have one table called 'vacancies' which has a 'vacancy_id' PK. It looks like this:

create table vacancies
(
vacancy_id int not null auto_increment,
org_id int not null,
name varchar(255) not null comment 'title',
vacancy_visibility_start_date datetime comment 'vacancy visibility date, when it needs to be active on the website',
vacancy_visibility_end_date datetime,
primary key (vacancy_id)
);


Following this I have a couple of other tables which are linked to this one.

create table vacancy_calendar
(
vacancy_calendar_id int not null auto_increment,
vacancy_id int,
date_from datetime not null,
date_to datetime not null,
primary key (vacancy_calendar_id)
);

create table vacancy_interests
(
vacancy_id int,
interest_id int
);
create table vacancy_skills
(
vacancy_id int,
skill_id int
);


All of these tables can contain multiple rows for the same vacancy_id.

My page has different filters which I want to process via AJAX.
I want to have one line per vacancy containing all data I need + it has to match my filtering criteria. However I am not sure how my query has to look like in order to retrieve the result I am looking for.
It is possible to filter on 'interest_id' , 'skill_id', 'date_from' and 'date_to'.

I started with the following query but I am stuck very fast:

SELECT v.*, vi.interest_id
FROM `vacancies` as v
INNER JOIN `vacancy_interests` as vi on v.vacancy_id = vi.vacancy_id
GROUP BY v.vacancy_id


This query will only return me 1 interest_id for a vacancy, even if the vacancy has 3 interest_id rows in the vacancy_interest table. If I remove the GROUP BY statement I will get 3 rows for the same vacancy which is not what I want either.

Ideally I would want the interest_id's to be each in a separate column or in the same field separated by comma's. Or if there are any other possibilities/suggestions feel free to share!

Answer

You can use group_concat for get interest_id separated by comma

SELECT v.*,  group_concat(vi.interest_id)
FROM `vacancies` as v
INNER JOIN `vacancy_interests` as vi on v.vacancy_id = vi.vacancy_id
GROUP BY v.vacancy_id