[SOLVED] syntax on multiple join problem?

[defeated]

Hi,

Tables and col names as follows

 

Main

id, name,  sub1_id, sub2_id, sub3_id

 

sub1

id, english

 

sub2

id, english

 

sub3

id, english

 

id's are all numbers.  Sub tables contain the english "translation" of the numbers.

If I want to get the english for all entries in a row on the main table....

 

$query=mysql_query("SELECT name, sub1.english, sub2.english, sub3.english FROM main
LEFT JOIN (sub1, sub2, sub3)
ON (main.sub1_id=sub1.id AND main.sub2_id=sub2.id AND main.sub3_id=sub3.id)
WHERE main.id='$selected_row'") or die(mysql_error());
$row=mysql_fetch_array($query);
$main_name=$row['name'];
$sub1_english=$row['sub1.english'];
$sub2_english=$row['sub2.english'];
$sub3_english=$row['sub3.english'];

 

Am I barking up the wrong tree?

[fenway]

Sounds like you're working with a non-normalized table design.

[defeated]

hmmm.  I thought I was normalising it!  :-\

The idea is that the 'english' is repeated many times in the table, so I replaced the english with an id number and add a translation table (id =>english). This significantly reduces the amount of data held in the db.

 

For example... the column may be job catagories.  The english may be one of a list (Medical and Healthcare jobs, Financial Jobs, Scientific and Technical Jobs.)

I replace them with an id number... eg 1,2,3. That reduces the number of chars needed from up to 50 varchars down to 2 ints.  I then have a table that goes

id    English

1    Medical and Healthcare Jobs

2    Financial Jobs

3    Scientific and Technical Jobs

 

 

 

Each job (eg one row in the main table) will have several of these tables for information that comes from a list.. eg. job type, job name,  job locations, salaries, experience levels, questions to ask applicant.

 

I am assuming that by normalisation you are talking about many to many relationships?  Because there are so many tables involved I can't get my head around that many tables.  I have done a many to many relationship for job tags (bit like the wordpress tags).  That was a b***h because I had to have the tags expire when the job did (but not delete until the job was actually deleted).  Kickstart set me straight on that one.

 

I just want a row per job that holds all the information, but in a simplified format (eg ints instead of varchars) where possible.  But when I recall the information I have to get the full varchar info from the separate pages.

 

I'm very lost on the multiple joins necessary.  Any enlightenment gratefully received!

 

 

 

 

[artacus]

Well just because you've made it harder doesn't mean you've normalized it

 

So I'm going to guess these are job interests a person may have. So what do you do when someone comes along with 4 job interests? or 5 or 6? Do you keep adding tables?

 

Instead do something like this:

CREATE TABLE person (
  person_id  INT NOT NULL PRIMARY KEY,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
);

CREATE TABLE job_interests(
  interest_id INT NOT NULL PRIMARY KEY,
  job_description VARCHAR(80) NOT NULL
);


CREATE TABLE person_job_interests (
  person_id  INT NOT NULL,
  interest_id INT NOT NULL,
  preference_no INT2,
  CONSTRAINT person_job_interests_pk PRIMARY KEY (person_id, interest_id)
);

 

Also "english" appears to be a poor choice for a column name here. Use something like 'job_category', 'label' or 'description'.

[defeated]

Sorry Artacus, I haven't explained it well.

It is a job board.

When inserting a job there is a form.  The form has several multiple selects or single selects from a select menu.  All the data then goes into the db.

 

examples. 

 

<select name='job_type' value='1'>Medical and Healthcare jobs</select> etc.

Then based on that selection the next select box changes its content to reflect jobs in that category.

 

Then there will be a list of salaries, a list of minimum experience, a list of locations.  It  is not possible to select something that is not in the list.

 

I had it so that the value in the select was the same as the text so I ended up with..

 

id    jobref  jobtitle      job_type                              job_role            min_exp    location

1    0908    Welder      Medical and Healthcare Jobs  medical welder    2-3 years  North London

 

Because that is only some of the fields the database was getting too large.  So I did this....

 

id    jobref  jobtitle    job_type                                job_role            min_exp    location

1    0908    Welder      1                                          56                    3              18

 

But then when I want to display a job I need to translate it back into the plain english.

 

Thus I need to do multiple joins to tables that contain the id's and the plain english.

 

Perhaps I am going about it arse-ways?  The trouble is that while there are only 1000 or so jobs the original version was ok, but if I get thousands of jobs I am using up more space than I need to.

 

I hope it is a bit clearer now.

 

 

 

'english' was just for my example.  The real fields do not make sense easily.

[kickstart]

Hi

 

Makes more sense with the added detail.

 

Basic syntax would be like this.

 

$query=mysql_query("SELECT name, sub1.english, sub2.english, sub3.english FROM main

LEFT JOIN sub1 ON main.sub1_id=sub1.id

LEFT JOIN sub2 ON main.sub2_id=sub2.id

LEFT JOIN sub3 ON main.sub3_id=sub3.id

WHERE main.id='$selected_row'") or die(mysql_error());

 

However you would probably want a column for job type within the job role table (ie, the id of the job type), probably removing job type from the main table

 

All the best

 

Keith

[defeated]

Cheers Keith.  That makes sense.  Thank you once again!  At this rate I'll have to credit you in the site code!