CODEWITHSUNDEEP
mysqlsql
Dennis
Dennis

Reputation: 3690

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!

Upvotes: 0

Views: 57

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

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

Referring to you comment about add where eg:

You can add where condition 

SELECT v.*,  group_concat(vi.interest_id)
FROM `vacancies` as v
INNER JOIN `vacancy_interests` as vi on v.vacancy_id = vi.vacancy_id
INNER JOIN `vacancy_skills` as vs ON vs.vacancy_id  = v.vacancy_id 
WHERE vs.skill_id IN (4) AND vi.interest_id IN (1,3)
GROUP BY v.vacancy_id

In this case the gorup_concat is applied on the resulting rows .. because group by perform the related action on the selected resulting rows .

Upvotes: 2

Related Questions