Results 1 to 4 of 4

Thread: [RESOLVED] SQL statement problem

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Resolved [RESOLVED] SQL statement problem

    I have three tables: Personal, Payments and Department with the following fields (primary keys are in bold)

    Personal - NameId, Surname, Forename
    Payments - NameId, DeptId, Pay
    Department - DeptId, DeptName , Floor

    data1.recordsource = Personal, Payments, Department

    SQL:

    "SELECT Personal.Surname, Payments.Pay, Department.DeptName
    FROM Personal, Payments, Department
    WHERE Department.DeptId = Payments.DeptId
    AND Personal.NameId = Payments.NameId
    AND Personal.Surname = 'Abbots'
    OR Personal.Surname = 'Potter' "

    For some reason when I use this string because Im calling data from more than one table I'm getting 80+ records instead of the 3 I should get

    Any Ideas on where I've gone wrong?
    Last edited by bubblegum_girl; Apr 16th, 2006 at 07:57 AM.

  2. #2
    Fanatic Member eimroda's Avatar
    Join Date
    Jul 2000
    Location
    Philippines
    Posts
    642

    Re: SQL statement problem

    try to use inner joins:
    SELECT blah blah
    FROM Personal INNER JOIN
    Payments ON Personal.NameID = Payments.NameID INNER JOIN
    Department ON Payments.DeptID = Department.DeptID
    WHERE.....
    On Error GoTo Hell

    Hell:
    Kill Me


    Food For Thought:

    - Do not judge a book... if you're not a judge!


  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Re: SQL statement problem

    Thanks mate I found away around it I forgot the brackets around the AND/OR


    "SELECT Personal.Surname, Payments.Pay, Department.DeptName
    FROM Personal, Payments, Department
    WHERE Department.DeptId = Payments.DeptId
    AND ( Personal.NameId = Payments.NameId
    AND Personal.Surname = 'Abbots'
    OR Personal.Surname = 'Potter' )"


  4. #4
    New Member
    Join Date
    Feb 2006
    Location
    Philippines - Cavite City
    Posts
    9

    Re: [RESOLVED] SQL statement problem

    Hmmm thks this topic helps a lot

Posting Permissions

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



Click Here to Expand Forum to Full Width