CODEWITHSUNDEEP
mysql
ngplayground
ngplayground

Reputation: 21617

MySQL filling out values from other tables

I have a table called vacancy which looks like so

enter image description here

role company location each have a table of there own but I would like to get a query together to pull the information from the other tables to fill in vacancy

I tried

SELECT  r.title, c.company, l.town, l.country, v.term
FROM role r, vacancy v, location l, company c

But this gives my 300 rows of the same title when i should in fact have 3 rows returning.

company Company

role role

location location

Upvotes: 0

Views: 65

Answers (2)

Eric Wu
Eric Wu

Reputation: 917

Beardy, the reason MySQL is showing 300 rows instead of 3 is because it returned all the data from the three tables simultaneously.
Considering the fact that role, company and location are columns containing the primary keys of the aforementioned tables, all you have to do is a join.
Also, considering that this table of yours, vacancyis the one from which you want to show the data, this is the query you should use (replace the column names with the ones you actually have in your database):

SELECT 
vacancy.id as '#',
role.name as 'Role', 
company.name as 'Company',
location.name as 'Location'
FROM
vacancy
LEFT JOIN role on role.ID = vacancy.role
LEFT JOIN company on company.ID = vacancy.company 
LEFT JOIN location on location.ID = vacancy.location

EDIT: I was about to submit this answer when Dianuj posted his... So, I decided to give my answer an extra boost before submitting it :D

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You can use join to display data from all four tables 

SELECT v.id, r.title, c.company, l.town, l.country, v.term
FROM  vacancy v
JOIN  location l ON (v.location = l.id)
JOIN   company c ON (v.company = c.id)
JOIN  role r ON (v.role = r.id)

Upvotes: 2

Related Questions