Left Join two tables---so slow!

oldnickj · #1 (**permalink**) 01-30-09, 17:35

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

oldnickj · #2 (**permalink**) 01-30-09, 17:56

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

r937 · #3 (**permalink**) 01-30-09, 22:30

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

hkp819 · #4 (**permalink**) 02-02-09, 00:25

these are the online sql learning site here you can take the help
SQL Tutorial
SQL Tutorials - Lesson 1: SQL Startup

Internet Services

Web Development

Digital Marketing

Consumer Tech