acc contains basic information about a customer (1 rec per acctno)
accmemo contains multiple Account Memo messages (0 or more recs per acctno)
accccard contains credit card(s) information (0 or more recs per acctno)
sdtik contains customer sales information (0 or more recs per acctno)
all tables have acctno as a key
I need to select the following information:
acc.strref, acc.acctno, acc.namelast, acc.namefirst, acc.adddate, accmemo.memo, accccard.credcardtype, accccard.credcardnum, count(distinct(sdtik.datein)) as visits, sum(if(sdtik.voidreason>0,0,sdtik.amt)) as sales
I need the following conditions met: 1) all accts where strref=1 2) all accmemo.memo fields, if any, for each acct 3) all credcardtype & credcardnum from accccard, if any, for each acct 4) count & sum from sdtik to be only records where datein>=20020701
Is there anyway to right this query? I am still fairly new in writing queries. At present, I am using perl to do this in multiple steps and queries. 1) select info from acc 2) for each acctno, query accmemo, 3) for each acctno, query accccard, 4) for each acctno, query sdtik, 5) display information. I know that 1 single query is more efficient than looping and processing 3 additional queries per acctno.
Any/All help will be greatly appreciated.
Thanks in advance.
Patrick J. Shoaf, Systems Engineer <mailto:[EMAIL PROTECTED]>[EMAIL PROTECTED]
Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or 888-638-6963 Fax: 724-489-4386
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
