You could use either an EXISTS clause when
selecting from your Job table
select *
>From Job
where
exists(select *
From JobLocation inner join Location ....
where
Location.ID = abc
and JobLocation.JobID = Job.JobID)
or
use a DISTINCT clause on the 3-table join if you
are just returning fields from the Job table. If you are
returning fields from either the Location or JobLocation
table, you'll still get Jobs listed multiple times.
Not sure which is the most efficient, but IIRC SQL
(S-Q-L?) server does more work performing a
DISTINCT over an EXISTS operation.
cheers
----- Original Message -----
From: "Sam Thompson" <[EMAIL PROTECTED]>
To: "ActiveServerPages" <[EMAIL PROTECTED]>
Sent: Tuesday, October 01, 2002 7:14 PM
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 PROTECTED]
