CODEWITHSUNDEEP
mysqlsql
hungneox
hungneox

Reputation: 9829

How to write a select query for this

I have two table like this

job(id,title,location,...)

job_page(id,id_job,id_page,page_name)

For example I want to write a select query to fill a table with following columns:

+_______+________+__________+_______+
|Job Id | Titlte | Location | Page  |
+_______+________+__________+_______+
|1      | Hot job| Somewhere| Page1 |
|       |        |          | Page2 |
+_______+________+__________+_______+

How can I write a select query for this?

I tried but it's not as I expected:

SELECT jp.page_name,j.* FROM jobs j
LEFT JOIN job_pages jp ON jp.id_job = j.id
GROUP BY j.id;

Upvotes: 0

Views: 94

Answers (2)

mlt
mlt

Reputation: 1659

If I understand you correctly, you'd like to put all the "pages" into a single cell. I guess the only thing you are missing is GROUP_CONCAT on j.page_name . If it is for web or something, you may also use SEPARATOR clause set to <br /> or something.

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

You need to use group_concat:

 SELECT j.id, j.title, j.location, group_concat(jp.page_name)
 FROM jobs j LEFT JOIN job_pages jp ON jp.id_job = j.id
 GROUP BY j.id, j.title, j.location;

In MySQL, you can abbreviate the GROUP BY:

 SELECT j.id, j.title, j.location, group_concat(jp.page_name)
 FROM jobs j LEFT JOIN job_pages jp ON jp.id_job = j.id
 GROUP BY j.id;

but this really bugs multi-database users like me.

Upvotes: 1

Related Questions