Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Patrick Danso
  Where is Patrick Danso?
 Accra
 Ghana
 Patrick Danso
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Only One Record
Thread ID: 345581 Message ID: 345595 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, May 24, 2012 6:26:07 PM         
   


> > Hi,
> > I want to select from a payments table all members who’s payments in the table are less than 3times for a particular type of payment (for example “Tax on Books”).
> >
> >
> > Thank you
>
>
>
>
> CREATE CURSOR Payments (pay_id i UNIQUE, member_id i, pay_date D, amount N(7,2), tax_code I)
> INSERT INTO Payments VALUES (1, 21,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (2, 23,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (3, 21,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (4, 22,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (5, 23,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (6, 23,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (7, 21,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (8, 24,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (9, 21,DATE()-70, 12.56,1)
> INSERT INTO Payments VALUES (10, 24,DATE()-70, 12.56,1)
> 
> CREATE CURSOR Taxes (tax_code i UNIQUE, descr C(20))
> INSERT INTO Taxes VALUES (1, 'Tax on books')
> INSERT INTO Taxes VALUES (2, 'Tax on bicycles') 
> 
> SELECT member_id,COUNT(*),SUM(amount) FROM Payments ;
> JOIN Taxes ON Payments.tax_code=Taxes.tax_code ;
> WHERE Taxes.descr LIKE 'Tax on books' GROUP BY member_id ;
>  HAVING COUNT(*) <3
> 
> CREATE CURSOR Members (member_id I UNIQUE, first_name C(6), last_name C(20), address C(10), post_code i)
> INSERT INTO Members  VALUES (21, 'John','Smith', 'H Street 4', 12345)
>  INSERT INTO Members  VALUES (22, 'Mary','Smith', 'A Street 89', 12399)
> INSERT INTO Members  VALUES (23, 'Bob','Brown', 'B Street 34', 12320)
> INSERT INTO Members  VALUES (24, 'Jacquie','Black', 'C Street 4', 12300)
> 
> SELECT DISTINCT M1.* FROM Members M1 JOIN Payments P1 ;
>  ON M1.member_id= P1.member_id ;
>  WHERE 3 > (SELECT COUNT(*) FROM Payments P2;
>  WHERE P1.member_id = P2.member_id )
> 

>
> -Anders


Hi Anders,
am very grateful. Will look at it and give you the feedback

ENTIRE THREAD

Only One Record Posted by Patrick Danso @ 5/24/2012 4:28:58 PM
RE: Only One Record Posted by Anders Altberg @ 5/24/2012 6:08:29 PM
RE: Only One Record Posted by Patrick Danso @ 5/24/2012 6:26:07 PM