Umm, I was gonna' say something like what Troy said, but, since he said it, I won't.
I'll add though that I often CREATE VIEWS as well, especially when I can see that one ViewName could be used to produce different results/reports for different stakeholders. Next is a probably-not-too-helpful sample ... CREATE VIEW vYPIDCounter + (YPID, + COSTCENT, + DSERV) AS + SELECT + (yp.YPID, + svc.COSTCENT, + adm.DSERV) + FROM + YP yp, + SERVICE svc, + EV adm + WHERE + yp.ypid = svc.ypid + AND + yp.ypid = adm.ypid I'd also suggest to everyone, who doesn't have it, picking up a copy of Joe Celko's "SQL For Smarties", as it contains lots of neat SQL stuff, the vast majority of which will run in RBase. The ones that don't, if I recall, are for the most part, from SQL (proposals) after ANSI-92. I recall the days when Mr. Celko used to submit Q's to RBase users as he would explore the idiosyncrasies and degrees of compliance among the different SQL vendors in one of the DB periodicals - I think I still have a bunch of 'em in my attic. I suspect that his book is an elaborated compilation of these articles. Later, Steve in Memphis > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of David Ebert > Sent: Wednesday, March 27, 2002 2:57 PM > To: [EMAIL PROTECTED] > Subject: Relational counting > > > I have a parent table of clients with a primary key of ypid. There is a > child table of client admissions with a foreign key of ypid, and a second > child table of services also with a foreign key of ypid. I'm trying to > count the number of clients appearing in the admission table more > than once > by the program value for individuals receiving services in a date > range from > the service table. > > This gives me the initial value (total clients): > > SELECT COUNT(*) INTO VCOUNTALL INDICATOR vctallind + > FROM YP WHERE YPID IN (SELECT YPID FROM SERVICE WHERE COSTCENT = .GVPROG + > AND DSERV BETWEEN .GVDATE1 AND .GVDATE2) > > This is how I'm asking the second part: > > SELECT COUNT(ypid) INTO VCOUNT2 INDICATOR VINDmult + > FROM yp WHERE ypid IN (SELECT ypid FROM service + > WHERE costcent = .gvprog AND dserv BETWEEN .gvdate1 AND .gvdate2) + > --This is where I think my query falls down. > and ypid IN (SELECT count(ypid) FROM ev + > HAVING COUNT(ypid) > 1 ) > > My goal being to subtract vcount2 from vcountall to get the number of > individuals admitted more than once in a period. The problem is that the > second query always returns 0. > > Am I missing a third step? How can I identify the record count > for a parent > table where the FK appears more than once in the Admissions table > (EV) with > services in a date range in the Service table? > > tia > > Dave > [EMAIL PROTECTED] > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
