I'll give those a try, thanks Tore and everyone for the help :) Sam
----- Original Message ----- From: "Bostrup, Tore" <[EMAIL PROTECTED]> To: "ActiveServerPages" <[EMAIL PROTECTED]> Sent: Tuesday, October 01, 2002 5:06 PM Subject: RE: quick database design question > Yes and no. I understand the question - and it is one that comes up every > now and then. But you have to decide *which* location to display. Do you > really want to display *any one* of the job locations, but NOT all of them? > Normally, that wouldn't make sense. > > However, assuming you have a reason, you could for instance specify that you > want to show the first location (by its sort order) by a query like: > > SELECT J.<jobinfo select list>, Min(L.Location) > FROM Jobs as J > INNER JOIN JobLocations as JL > ON JL.JobID = J.JobID > INNER JOIN Locations > ON L.LocationID = JL.LocationID > GROUP BY J.<JobInfo select list> > > If you don't care about the location, you just want to make sure that there > is at least one location defined, use EXISTS as suggested: > > SELECT J.<jobinfo select list> > FROM Jobs as J > WHERE Exists (SELECT 1 FROM JobLocations as JL > INNER JOIN Locations > ON L.LocationID = JL.LocationID > AND JL.JobID = J.JobID ) > > HTH, > Tore. > > > -----Original Message----- > From: Sam Thompson [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, October 01, 2002 10:45 AM > To: ActiveServerPages > Subject: Re: quick database design question > > > I want to only display the job once, but I need to inner join the other > tables so i can display the location to the user. It doesnt really matter > which location I display, as long as the actual job is only listed once. > > Does that make sense? > > Thanks, Sam > > > (Tore, this is a carry on from my question on friday about group > by/cartesian product ...thanks for your replies on that.) > > ----- Original Message ----- > From: "Bostrup, Tore" <[EMAIL PROTECTED]> > To: "ActiveServerPages" <[EMAIL PROTECTED]> > Sent: Tuesday, October 01, 2002 2:51 PM > Subject: RE: quick database design question > > > > What results are you looking for, and what does your query look like? > > > > An inner join on the three tables would give you every job at every > > location. If that is not what you want to show, you have to add other > > criteria to limit your result set, or not join the tables if you are only > > looking for job info and not location info. > > > > HTH, > > Tore. > > > > -----Original Message----- > > From: Sam Thompson [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, October 01, 2002 5:15 AM > > To: ActiveServerPages > > Subject: Re: quick database design question > > > > > > Cheers! > > > > The reason I ask is because when I run a query against the job table, and > I > > inner join the locations and locations lookup table I get the same job > > listed many times, presumably because it is listed many times in the > > locations lookup table. > > > > Any ideas on how I can stop it being listed multiple times? > > > > Thanks > > > > Sam > > > > ----- Original Message ----- > > From: "Daniel Field" <[EMAIL PROTECTED]> > > To: "ActiveServerPages" <[EMAIL PROTECTED]> > > Sent: Tuesday, October 01, 2002 9:48 AM > > Subject: RE: quick database design question > > > > > > > Yep, looks ok to me! > > > > > > -----Original Message----- > > > From: Sam Thompson [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, October 01, 2002 09:45 > > > To: ActiveServerPages > > > Subject: quick database design question > > > > > > > > > I just wanna double check that im doing the right thing! > > > > > > Ive got a database which contains job vacancies, and each job can be in > > > multiple locations around the world. > > > > > > So I need 3 tables right? > > > > > > 1 containing the job ID, job title etc, another containing the list of > > > locations and a location ID, and the last one which contains the job ID > > and > > > the location ID (lookup table). > > > > > > Is that the correct way of doing it? > > > > > > Thanks! > > > > > > Sam Thompson > > > > > > > > > --- > > > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > > %%email.unsub%% > > > > > > _____________________________________________________________________ > > > This e-mail has been scanned for viruses by the WorldCom Internet > Managed > > > Scanning Service - powered by MessageLabs. For further information visit > > > http://www.worldcom.com > > > > > > > > > > > > _____________________________________________________________________ > > > This e-mail has been scanned for viruses by the WorldCom Internet > Managed > > Scanning Service - powered by MessageLabs. For further information visit > > http://www.worldcom.com > > > > > > --- > > > You are currently subscribed to activeserverpages as: > > [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > %%email.unsub%% > > > > > > > > > --- > > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > > %%email.unsub%% > > > > --- > > You are currently subscribed to activeserverpages as: > [EMAIL PROTECTED] > > To unsubscribe send a blank email to > %%email.unsub%% > > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > %%email.unsub%% > > --- > You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
