Reducing number of JOINs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Reducing number of JOINs

Post by JayBird »

The following query returns the results that i need, but i can't believe there isn't a better way of acheiving the same results by only joining once

Code: Select all

SELECT * FROM `vacancies` as `v`
INNER JOIN `options` as `o1` ON `o1`.`optionID` = `v`.`jobTitle`
INNER JOIN `options` as `o2` ON `o2`.`optionID` = `v`.`jobLocation`
INNER JOIN `options` as `o3` ON `o3`.`optionID` = `v`.`jobBusiness`
GROUP BY `v`.`jobID`
Something along the lines of

Code: Select all

SELECT * FROM `vacancies` as `v`
INNER JOIN `options` as `o` ON `o`.`optionID` = `v`.`jobTitle`, `o`.`optionID` = `v`.`jobLocation`, `o`.`optionID` = `v`.`jobBusiness`
GROUP BY `v`.`jobID`
My tables

Code: Select all

vacancies  CREATE TABLE `vacancies` (                                                                                                                                                                                                                                                                                                                                                                   
      `jobID` int(10) NOT NULL auto_increment,                                                                                                                                                                                                                                                                                                                                                   
      `jobTitle` varchar(255) default NULL,                                                                                                                                                                                                                                                                                                                                                      
      `closingDate` int(11) default NULL,                                                                                                                                                                                                                                                                                                                                                        
      `adText` text,                                                                                                                                                                                                                                                                                                                                                                             
      `refNo` varchar(255) default NULL,                                                                                                                                                                                                                                                                                                                                                         
      `jobLocation` int(3) default NULL,                                                                                                                                                                                                                                                                                                                                                         
      `datePosted` int(11) default NULL,                                                                                                                                                                                                                                                                                                                                                         
      `jobBusiness` int(3) default NULL,                                                                                                                                                                                                                                                                                                                                                         
PRIMARY KEY  (`jobID`)                                                                                                                                                                                                                                                                                                                                                                     
) TYPE=MyISAM

Code: Select all

options  CREATE TABLE `options` (                                                                                                                                                                           
      `optionID` int(10) NOT NULL auto_increment,                                                                                                                                                      
      `optionCat` varchar(255) default NULL,                                                                                                                                                           
      `optionName` varchar(255) default NULL,                                                                                                                                                          
      PRIMARY KEY  (`optionID`)                                                                                                                                                                        
) TYPE=MyISAM
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Reducing number of JOINs

Post by timvw »

Am i missing something? You're joining optionID, int(10) = jobTitle, varchar(255) ?
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

I don't see the logic behind your tables... You are trying to join an optionID "INT(10) NOT NULL auto_increment" to a JobTitle "VARCHAR(255)", a job location "INT(3)", or a JobBusiness "INT(3)"...

Seems like a very strange thing that you want to do.

Anyway, that said, have you tried using one join with or statements like

Code: Select all

SELECT * FROM vacancies v
INNER JOIN options o1 ON (o1.OptionID = v.JobTitle OR o1.OptionID = v.JobLocation OR o1.OptionID = v.JobBusiness)
GROUP BY v.JobID
?

EDIT: Beaten to it by timvw :)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

ah, yes, sorry....i have just changed the relationships between tables and had not changed the data types when those CREATE functions were taken.

Asume they are the correct data types now, they are now all int(3)

GM: Tried that already, it returns no results

Basically, just to clarify what i am trying to do. I have a `vacancies` table that holds information for a number of jobs.

jobTitle, jobLocation and jobBusiness all store an id that releates to information in the `options` table.

So basically when selecting the data from the `vacancies` table, i also want to pull the correct data associated which each of these ids

cut-down example
if i had a table that contained the following data

Code: Select all

table: vacancies
jobID      jobTitle      jobLocation      jobBusiness
1          1             2                 3

Code: Select all

table: options
optionID      optionName
1             Electrician
2             London
3             Electronics
When selecting all the jobs from the `vacancies` table, i want the ids to be replaced with the corresponding data from the options table, referenced by the `optionID`
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post by GM »

In this case, the only way to do it is to join the table 3 times, as you have been*

Might I suggest though that you could improve your database structure if you have a table for Job Titles (ID_JOBTITLE, DE_JOBTITLE) a table for locations (ID_LOCATION, DE_LOCATION) and a table for Job Business (ID_JOBBUSINESS, DE_JOBBUSINESS).

Then, in your main table, you use

Code: Select all

ID_JOB         ID_JOBTITLE    ID_LOCATION    ID_JOBBUSINESS 
1             1              1               1
2             1              2               1
...
In this way, your database is better normalised, and the structure is more stable and controllable.

* To see why, look at the above suggested structure - in this structure to get the descriptions instead of the codes, you have to join three tables. It is the same if you had all the descriptions in 1 table, you need to join it 3 times, as if it were three different tables.
Post Reply