Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Left Join two tables---so slow!

    I'm trying to find all members in the CBA table who have a record in either the DUES_PAID table with 2008 in the DUES_Year column or who have 2009 in the itemname column of paypal_payment_info.

    This works but takes about two minutes! Any suggestions for speeding this up?

    Nick

    SELECT c.CBA_ID, c.LNAME, c.FN_MI, p.itemnumber, d.DUES_YEAR
    FROM ((CBA c
    LEFT JOIN DUES_PAID d ON d.CBA_ID=c.CBA_ID)
    LEFT JOIN paypal_payment_info p ON p.custom=c.CBA_ID)
    WHERE d.DUES_YEAR =2008 OR p.itemnumber = 2009

    ORDER BY c.LNAME ASC

  2. #2
    Join Date
    Jan 2009
    Posts
    124
    In reality want to see if one member, "3667" from the CBA table has paid his dues in one table or the other.

    SELECT c.CBA_ID, c.LNAME, c.FN_MI, p.itemnumber, d.DUES_YEAR
    FROM ((CBA c
    LEFT JOIN DUES_PAID d ON d.CBA_ID=c.CBA_ID)
    LEFT JOIN paypal_payment_info p ON p.custom=c.CBA_ID)
    WHERE c.CBA_ID = 3667 AND d.DUES_YEAR =2008 OR p.itemnumber = 2009

    ORDER BY c.LNAME ASC

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's another approach...
    Code:
    SELECT c.CBA_ID
         , c.LNAME
         , c.FN_MI
         , 2008     AS year_paid
      FROM CBA AS c
    INNER
      JOIN DUES_PAID AS d 
        ON d.CBA_ID = c.CBA_ID
       AND d.DUES_YEAR = 2008
     WHERE c.CBA_ID = 3667 
    UNION ALL
    SELECT c.CBA_ID
         , c.LNAME
         , c.FN_MI
         , 2009     AS year_paid
      FROM CBA AS c
    INNER
      JOIN paypal_payment_info AS p 
        ON p.custom = c.CBA_ID
       AND p.itemnumber = 2009
     WHERE c.CBA_ID = 3667
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2008
    Posts
    59
    these are the online sql learning site here you can take the help
    SQL Tutorial
    SQL Tutorials - Lesson 1: SQL Startup

Posting Permissions

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