Posted by Jackson Linux on 10/04/31 11:10

First, I apologize - I'm new to this, grateful for the list and just
confused. I didn't mean to post improperly.

Second, thanks so much Rory for simplifying it and answering that
anyway. I promise I wasn't going for technical impressiveness and
easier is definitely better!

Thanks again,
Jack
On 9 Mar 2005, at 02:19, Rory McKinley wrote:

> Jackson Linux wrote:
>> Hi, all,
>> I have three tables; 'cv', the main table, 'jobcat', a definition
>> table and 'cvjobcats', an intersection table (for more detail see PS
>> below).
>> I'd like to join this all together to be able to make a headline
>> consisting of the plain English description of the job category
>> followed by all rows within cv which contain a reference to that job
>> category
>> I can follow this only to a point and that is where I ask for the
>> List's help - does this even resemble the beginnings of what I want
>> to accomplish? I've tried to comment as I go for my own sanity
>> //Select columns from 'cv', whose 'category' references numbers
>> //Select 'category' from 'jobcat' to translate the numbers to English
>> "SELECT cv.cv_id,
>> cv.category,
>> dates,
>> cv.job_title,
>> cv.company,
>> cv.job,
>> jobcat.category
>> FROM cv
>> LEFT JOIN cvjobcats
>> ON cvjobcats.cv=cv.cv_id //Let tables cv and jobcat understand each
>> other.
>> //This next bit I'm confused about
>> LEFT JOIN jobcat
>> ON jobcat.jobcat_id=cvjobcats.jobcat
>> WHERE
>> cvjobcats.jobcat='4'";
>> Great. I'm now lost. Can anyone advise?
>> Thanks in advance,
>> PS:
>> <far too much information>
>> cv contains columns including an primary key ('cv_id') and a column
>> called 'category' which refers to job categories by number.
>> jobcat contains two fields: a primary key (jobcat_id) and a plain
>> english description
>> cvjobcat contains two key columns: 'cv_id' and 'jobcat_id'.
>> </far too much information>
>
> Hello Jackson
>
> Are there any circumstances under which the cvjobcats table would have
> a job category entry that does not appear in the jobcats table? IF
> not, you really don't need to use the LEFT JOINS. Instead you coudl do
> something like this:
>
> SELECT cv.cv_id,cv.category,dates,cv.job_title,cv.company,cv.job,
> jobcat.category
> FROM cv, cvjobcats, jobcats
> WHERE cvjobcats.cv=cv.cv_id AND cvjobcats_jobcat = 4 AND
> jobcat.jobcat_id=cvjobcats.jobcat
>
> Less technically impressive perhaps but easier to follow ;)
>
> Regards
>
> Rory
>
>

[Back to original message]