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

    Join Date
    Mar 2004
    Posts
    18
    Rep Power
    0

    Multiple left joins


    I want to join 6 tables 5 of which may or may not have related records. I am having trouble with my SQL though because I can't get the query to return less than 20,000 records. I believe it should be returning 50. The number of rows in each table is as follows:

    Code:
    A --  1 row
    B --  4 rows
    C -- 10 rows
    D -- 10 rows
    E --  5 rows
    F -- 20 rows
    This is the the starting point for the query:

    Code:
    select A.E_ID EXPERT_ID,
                 A.E_FIRST_NAME FIRST_NAME,
                 A.E_LAST_NAME LAST_NAME,
                 A.E_DEPT_ID DEPT,
                 substr(A.E_OFFICE_PHONE, 1, 3) OFFICE_PHONE1,
                 substr(A.E_OFFICE_PHONE, 4, 3) OFFICE_PHONE2,
                 substr(A.E_OFFICE_PHONE, 7, 4) OFFICE_PHONE3,
                 A.E_EMAIL EMAIL,
                 substr(A.E_HOME_PHONE, 1, 3) HOME_PHONE1,
                 substr(A.E_HOME_PHONE, 4, 3) HOME_PHONE2,
                 substr(A.E_HOME_PHONE, 7, 4) HOME_PHONE3,
                 A.E_WEB_SITE WEB_SITE,
                 A.E_ACAD_RANK_PREFIX ACAD_RANK_PREFIX,
                 A.E_TITLE TITLE,
                 A.E_SUPER SUPER,
                 A.E_SUPER_EMAIL SUPER_EMAIL,
                 A.E_STARTED_ON STARTED_ON,
                 A.E_INTERVIEWS INTERVIEWS,
                 A.E_ESSAYS ESSAYS,
                 A.E_SPEAKING SPEAKING,
                 A.E_KEYWORDS KEYWORDS,
                 A.E_QUALIFICATIONS QUALIFICATIONS,
                 A.E_ACAD_RANK ACAD_RANK,
                 B.ECP_CONTACT_PREF_ID CONTACT_PREF,
                 C.EPP_POSITION PRIOR_POS,
                 C.EPP_LOCATION PRIOR_POS_LOC,
                 C.EPP_NP PRIOR_POS_NP,
                 D.ER_RESEARCH_AREA RESEARCH,
                 E.EE_COL_UNIV COL_UNIV,
                 E.EE_DATES_ATTENDED COL_UNIV_DATES,
                 E.EE_DEG COL_UNIV_DEG,
                 E.EE_DEG_YEAR COL_UNIV_DEG_YEAR,
                 E.EE_MAJOR COL_UNIV_MAJOR,
                 F.EE_CATEGORY EXTRA_CATEGORY,
                 F.EE_DESCRIPTION EXTRA_DESCRIPTION
          from EXPERT A
          left join EXPERT_CONTACT_PREF B
                 on A.E_ID = B.ECP_EXPERT_ID
          left join EXPERT_PRIOR_POS C
                 on A.E_ID = C.EPP_EXPERT_ID
          left join EXPERT_RESEARCH D
                 on A.E_ID = D.ER_EXPERT_ID
          left join EXPERT_EDUCATION E
                 on A.E_ID = E.EE_EXPERT_ID
          left join EXPERT_EXTRA F
                 on A.E_ID = F.EE_EXPERT_ID
          where A.E_NPCUID = 'robersot'
    Any guidance is much appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    take your query and split it into several queries -- basically, one for each join

    notice that you are joining A to each of the other tables in turn

    if any of the individual queries returns more than one row for each row of A, there's your problem

    you're getting cross join effects

    if a single A row has 3 matching B rows and 4 matching C rows, then there will be 12 rows in the result set

    compound this by the other tables and it's easy to see why you're getting 20,000 rows

    Comments on this post

    • medialint agrees : I've done that by accident too ... how can we have 1,000,000 records for 20,000 people!!!??? Seems a common trapping.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo