Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003

    Unanswered: wierd query please help

    ok I have a database that has couple of tables the two i need help with our.

    Patientinfo and Doctorid

    in the doctorid table it has fields lastname and firstname

    in the patientinfo table i have one field named docname

    i have redesigned this database to make a user pick the doctor from the doctorid table so there are no longer 5 different ways of spelling his name .

    so this is what i need to clean it up , i need a query that looks at all the
    doctors names in the patientinfo table in field docname and then compares it to all the "lastname" & "Firstname" fields in table doctorid using maybe a third field to merge lastname and firstname to one field and then give me the ones that arent there. so if there are names in the doctorid table not in patientinfo i wanna know and vica versa.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    assuming the "doctorid" table has a primary key,
    select P.docname
      from patientinfo as P
    left outer
      join doctorid as D
        on P.docname in
           ( D.lastname & ' ' & D.firstname
           , D.lastname & ',' & D.firstname
           , D.lastname & ', ' & D.firstname
           , D.firstname & ' ' & D.lastname 
           , D.lastname
           , D.firstname )
     where D.pkey is null | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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