multiple inner joins on 1 table?

[mike182uk]

Hi there, i am doing an innerjoin on some tables. basically i have 1 main table that i am retrieveing data from. The columns in the table reference the id's of another table. multiple columns in the db referenece different id's in the second table. now to achieve getting the results i want i am finding myself doing 3 inner joins on the same table. is there an easier to do this? here is the sql i am using:


SELECT
locationcounty.Description AS location,
pv1.Description AS industry,
pv2.Description AS type,
pv3.Description AS salaryfrequency,
vacancies.JobTitle,
vacancies.Deadline,
vacancies.LocationTown,
vacancies.JobDescription,
vacancies.SalaryText
FROM
vacancies <--MAIN DB
Inner Join vacancies_location_counties ON vacancies.ID = vacancies_location_counties.VacancyID
Inner Join locationcounty ON vacancies_location_counties.LocationCountyID = locationcounty.ID
Inner Join picklist_values AS pv1 ON vacancies.JobBoardCategoryID = pv1.ID
Inner Join picklist_values AS pv2 ON vacancies.JobTypeID = pv2.ID
Inner Join picklist_values AS pv3 ON vacancies.SalaryFrequencyID = pv3.ID
WHERE
vacancies.ID = '37'"


so the picklist_values contains multiple values that i need. and ther all unique id's. This is a rubbish db design but the client insists i use it and i cannot alter any part of the db.

from the sql above im getting the results i need, it just seems a little long winded and i wondered if ther was a better way? (except redesigning the db)

[Fumigator]

You can do it that way, but you don't need to, and it will give you unnecessary duplicate results (in the case more than one of those three conditions are met).

I would join picklist_values once and put each of those three conditions in the join condition.


vacancies <--MAIN DB
Inner Join vacancies_location_counties ON vacancies.ID = vacancies_location_counties.VacancyID
Inner Join locationcounty ON vacancies_location_counties.LocationCountyID = locationcounty.ID
Inner Join picklist_values AS pv
ON vacancies.JobBoardCategoryID = pv.ID
OR vacancies.JobTypeID = pv.ID
OR vacancies.SalaryFrequencyID = pv.ID

[mike182uk]

Hey Fumigator thanks for the reply. well i did this the first time and it didnt work. the problem is in the picklist_value table, there are 2 columns: ID and Description. in the vacancys table JobBoardCategoryID refers to an ID in the picklist_value table, and i want the description. easy. but i then need to pull another two pieces of information from the same table using 2 different id's. It almost as if im requerying it. But all from one query. it sounds stupid i know.

With using our code, it will get the result if one of the three conditions are true (which all 3 are) and only pull 1 description. not 3 seperate pieces of information.

[Old Pedant]

No, Fumigator's answer is really wrong.

He missed this in your code:
SELECT
locationcounty.Description AS location,
pv1.Description AS industry,
pv2.Description AS type,
pv3.Description AS salaryfrequency,,,


In order to get those 3 different values, you *MUST* do the join to picklist_values table 3 times. No choice. And it's not really inefficient, assuming that the ID field in that table is the primary key. In fact, it's the only efficient way to code this.

Fumigators answer only works if all you wanted to do was find out if one or more of those fields in your vacancies table existed in the picklist_values table.

Yes, I agree that FROM A DESIGN VIEWPOINT there should be three separate tables (for JobBoardCategory, JobType, and SalaryFrequency), but actually you'll probably find that the query executes faster this way, is the index for the picklist_values table will probably fit into a single buffer in MySQL's memory cache and so all 3 ID's can be located very quickly from a single load of the index from disk. With three separate tables, at a minimum you'd have to load in all three indexes. So it may look funny, and may violate normalization rules, but it's surely pretty speedy to do it this way.

[mike182uk]

hey old pedant thanks alot for the reply. yeh i guess your right and yes the id field is the primary key. so i guess it is quite effiecient.

Im Just always weary when i find myself repeating lines of code.

Anyways thanks alot for the help

[Fumigator]

Yikes I really missed the mark on that one. I was just thinking about the number of rows that match up and figured you wouldn't want the duplicates. But you actually do, since each "duplicate" will return different values from the 2nd table; the only duplicated data will be from the first table. My bad :o