#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Pa
    Posts
    19
    Rep Power
    0

    SQL Query giving duplicate records


    I'm trying to pull from our database all the students that are absent on a certain day. I have it where I get all the students but it's duplicating records and I don't know why.

    Code:
    select emf.L_NAME,emf.F_NAME, atn.ID_NUMBER, atn.SAF_CODE, atn.SAF_DATE1
    from EMF 
    INNER Join ATN 
    ON EMF.ID_NUMBER=ATN.ID_NUMBER 
    Where atn.SAF_CODE='ne' and atn.SAF_DATE1='1-22-2013'
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    most likely because there are multiple ATNs for each EMF (whatever they are)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Pa
    Posts
    19
    Rep Power
    0
    Originally Posted by r937
    most likely because there are multiple ATNs for each EMF (whatever they are)
    If that's the case then what can I do about it?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Pa
    Posts
    19
    Rep Power
    0
    So how would I do this then.

    I have three tables that I need information from.

    ATN where I need attendance code, attendance date, id_number of all the students that are absent on todays date

    Contact Table where I need the phone number for the ID number from the ATN table

    and the EMF table where I need the first name and last name of the ID number that I am getting from the ID_Number from the ATN table
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    suddenly, there are three tables instead of two

    the duplicates per student are doubtless coming from duplicate contacts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo