>-----Original Message-----
>From: Jon Barker [mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, August 14, 2001 8:28 PM
>To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
>Subject: Re: SQL Statement - Selecting details if they exist if not...
>
>
>
>I'm not completely sure I understand you but:-
>
>select * from Jobs left outer join Users on Jobs.JobID=Users.JobID;
>
>Should give all the jobs, with null user data where there isn't a
>user associated with a job.
Actually that will give all job records regardless of whether there is a
matching JobID in the Users table or not. Depending on the Application a
LastViewedDate would only have a value if a record existed.
>From the case presented LastViewedDate would be filtered anyway, as there
would seem to be the possibility that there *could* be more than one
UserName per JobId, even when selecting only one user. I must admit that I
don't get the structure of this.
--Neil
>
>Jon
>
>> Hiya all,
>>
>> I have (for illustration purposes) two tables in an ODBC database:
>> Table 'Jobs'=JobID,JobDescription,JobDate
>> Table 'Users'=UserName,JobID,LastViewedDate
>>
>> Basically I want to select all JobID's and JobDescriptions from Jobs
>> where the user has a 'lastvieweddate' within the last 10 days.
>>
>> That bit I can work out - the problem is, is if there isn't an entry
>> in the 'Users' table for a particular JobID. I could include
>nulls for
>> the UserName for each jobid in the Users table and have an
>SQL statement in
>> the format:
>> ...AND (Users.UserName='' OR Users.UserName='John Doe')...
>>
>> But that may give me two records - which I don't really want. I could
>> temporarily store the duplicates in a Perl array or hash and remove
>> duplicates that way, but I would prefer a 'pure' SQL solution.
>>
>> Therefore, the question is:
>> * Is there any way of SELECTing Jobs.JobID, Jobs.JobDescription, and
>> Jobs.JobDate with Users.LastViewedDate if the UserName is found in
>> the Users table complete with the corresponding JobID. If there
>> isn't a Users.UserName.Users.JobID=Jobs.JobID entry, still return
>> the Jobs.JobID, Jobs.JobDescription and Jobs.JobDate details.
>>
>> Many thanks,
>> Richy C.
>> [speaking personally]
>> --
>> Richard Chiswell, Systems Developer.
>> Cradley Print Group.
>> All opinions and comments expressed are NOT necessarily
>those of the Cradley
>> Print.
>>
>>
>> --
>> The information contained within this e-mail sent by Cradley
>Print Ltd. is
>> confidential and is intended for the named recipient only.
>If you are not
>> the intended recipient please notify us by telephone
>immediately on 01384
>> 414100 (UK)or +(44)1384 414100 (International) or return it
>to us by e-mail
>> quoting the name of the sender and the addressee. Please
>then delete it from
>> your system.
>>
>> Encryption and Viruses
>> ==================
>> Please note that this e-mail and any attachments have not
>been encrypted.
>> They may therefore be liable to be compromised. Please also
>note that it is
>> your responsibility to scan this e-mail and any attachments
>for viruses.
>> Viruses and compromises of security are inherent risks in relation to
>> e-mail.
>>
>> We do not, to the extent permitted by law, accept any
>liability (whether in
>> contract, negligence or otherwise) for any virus infection
>and/or external
>> compromise of security and/or confidentiality in relation to
>transmissions
>> sent by e-mail.
>>
>> Contracts
>> ========
>> Please note, that contracts may NOT be concluded on behalf
>of Cradley Print
>> Ltd by e-mail, but contracts on behalf of our clients may be
>concluded by
>> e-mail.
>>
>
>
__________________________________________________________________________
Please Note :
Only the intended recipient is authorised to access or use this e-mail.
If you are not the intended recipient, please delete this e-mail and notify
the sender immediately. The contents of this e-mail are the writer's
opinion and are not necessarily endorsed by the Gunz Companies
unless expressly stated.
We use virus scanning software but exclude all liability for viruses or
similar in any attachment.
