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)
