Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: 3 tables wildcard searches

    Hi,

    I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

    There are 3 tables involved, the jobs, the applicants and the applications.

    I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

    There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

    My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

    So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

    Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

    So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?

    -PatP

  3. #3
    Join Date
    Apr 2006
    Posts
    33
    If I understand the question correctly, something like this might work:

    create proc usp_search(@searchstring varchar(100))
    as
    select * -- replace with required cols
    from applicants
    join applications on applicants.applicantid = applications.applicantid
    join jobs on applications.jobid = jobs.jobid
    where forename like @searchstring + '%' -- Auto Wild Card (Begins With)
    or surname like @searchstring + '%'
    or jobtitle like @searchstring + '%'
    or jobref = @searchstring
    or applicantid = @searchstring

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •