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)
