You'd have 3 tables:
Job
---
JobId (PK)
JobName
etc.
Industry
--------
IndustryId (PK)
IndustryName
etc.
JobIndustry
-----------
JobId (PK)
IndustryId (PK)
Then your query would look like:
SELECT J.JobId, JobName, IndustryName
FROM Job J INNER JOIN JobIndustry JI ON J.JobId = JI.JobId
INNER JOIN Industry I ON JI.IndustryId = I.IndustryId
Now, this will give you a recordset with multiple records per job (whereas
your table would give you just one record), so you'd need to deal with that.
You could loop through the query, or you could use the ValueList() function.
So you don't have the IndustryName text field in the JobIndustry table.
It's sole purpose is to implement the many-to-many relationship between jobs
and industries. You don't need any fields in there at all other than the
JobId and the IndustryId, but you may think of some that might be useful,
such as a date field showing when the relationship was created.
Does that clarify it a bit? Let me know if you have more questions.
Bob
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of carina cojeen
> Sent: Tuesday, 5 February 2002 2:18 p.m.
> To: [EMAIL PROTECTED]
> Subject: Re: [CFTALKTor] stumped on SQL
>
>
> right, because you cannot nest outer joins, right?
>
> Bob, can you give a little more clarification on how i'd use the
> jobindustry
> table? Would this table have the industry text value as well?
>
> JOBINDUSTRY
> JobID IndustryID IndustryName
>
> or is there something else i'm missing?
>
> crc
>
>
> Bob Silverberg wrote:
>
> > Well, you really _should_ have a separate JobIndustry table, with each
> > record's primary key being JobId + IndustryId. That way you
> could have as
> > many (or as few) industries per job as you like. It would also
> make your
> > join a bit simpler as you'd only have one IndustryId per record.
> >
> > Having said that, you can solve your problem with a SQL
> statement along the
> > lines of:
> >
> > SELECT JobId, I1.IndustryName, I2.IndustryName, I3.IndustryName, etc.
> > FROM JOBS INNER JOIN INDUSTRIES I1 ON JOBS.Industry = I1.IndustryId
> > INNER JOIN INDUSTRIES I2 ON JOBS.Industry2 = I2.IndustryId
> > INNER JOIN INDUSTRIES I3 ON JOBS.Industry3 = I3.IndustryId
> >
> > Now, if you don't have a valid IndustryId in each of Industry,
> Industry2 and
> > Industry3, you'll need to do an outer join, rather than an
> inner join (which
> > is another reason to go with the JobIndustry associative table).
> >
> > Hope that helps,
> > Bob
> >
> > > -----Original Message-----
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> > > Behalf Of carina cojeen
> > > Sent: Tuesday, 5 February 2002 1:35 p.m.
> > > To: CFTALK
> > > Subject: [CFTALKTor] stumped on SQL
> > >
> > >
> > > Hi all,
> > >
> > > Hoping someone can help me out here with a more complex SQL query...
> > >
> > > I'm sure it's not that hard but I'm just stumped...
> > >
> > > I have normalized the database for the following in order to save room
> > > in the JOBs table:
> > >
> > > JOBS table
> > > - contains 3 columns with the primary ID of an industry:
> > > Industry - IndustryID from INDUSTRIES table
> > > Industry2 - another IndustryID from INDUSTRIES table
> > > Industry3 - yet another IndustryID from INDUSTRIES table
> > >
> > > INDUSTRIES
> > > IndustryID - primary key - integer
> > > IndustryName - text value
> > >
> > > Now, of course, when I pull a query, I want to set up the
> query to show
> > > the IndustryName which is text, instead of the integer.
> > >
> > > When there was only one Industry column in the Jobs table, that was
> > > fine, I could do a simple table join... but now that I have
> 3 separate
> > > Industry columns, how do I set up the query to have the extra text
> > > IndustryName values?
> > >
> > > help help help???
> > >
> > > thanks!
> > > carina
> > >
> > > --
> > > Carina R. Cojeen
> > > x a r d e s i g n s
> > > web programming and design
> > > www.xardesign.com
> > > c. 416.434.8823
> > > h. 416.778.6024
> > >
> > >
> > > -
> > > You are subscribed to the CFUGToronto CFTALK ListSRV.
> > > This message has been posted by: carina cojeen <[EMAIL PROTECTED]>
> > > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> > > Manager: Kevin Towes ([EMAIL PROTECTED])
> > http://www.CFUGToronto.org/
> > This System has been donated by Infopreneur, Inc.
> > (http://www.infopreneur.net)
> >
> > -
> > You are subscribed to the CFUGToronto CFTALK ListSRV.
> > This message has been posted by: "Bob Silverberg"
> <[EMAIL PROTECTED]>
> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> > Manager: Kevin Towes ([EMAIL PROTECTED])
http://www.CFUGToronto.org/
> This System has been donated by Infopreneur, Inc.
> (http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: carina cojeen <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "Bob Silverberg" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
