List:General Discussion« Previous MessageNext Message »
From:Patrick Shoaf Date:July 9 2003 3:48pm
Subject:Query across multiple tables
View as plain text  
I have four tables I need to query for information; 
acc,accmemo,accccard,sdtik where
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

Midmon Internet Services, LLC
100 Third Street
Charleroi, PA 15022
Phone: 724-483-2400 ext. 105
  or    888-638-6963
Fax:   724-489-4386

Query across multiple tablesPatrick Shoaf9 Jul