0

My database knowledge is pretty limited but im hoping this will work......

The idea is to have a database for customers who have their grass cut fortnightly. The database will be mail merged to form bills every month or so...

So far I've constructed a database with the following tables:

Customer (CustomerID, Name, Address, Post Code,PricePerCut)
GrassCut (CustomerID, DateOfCut, Paid : boolean)

Each GrassCut has the CustomerID and date for the cut along with whether or not the customer has paid for the cut.

The idea is, that i construct an SQL query to represent all the grass cuts where paid is false so to create a table with Customers Name address and dates of all the cuts they have not yet paid for.

Is such a query possible (and if so what is it?) on the tables I have or would i need to restructure the database?

4
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by Atheeth
0
select 
	c.customerid, c.name, c.address, c.postcode, c.pricepercut, g.dateofcut, g.paid
from 
	grasscut g
inner join 
	customer c
on 
	c.customerid = g.customerid
where 
	g.paid = 0

you might need to change g.paid = false, depending upon your column type

0

Logi, just as an added point; might be worth adding a PriceCharged column in the GrassCut table. When adding an entry to GrassCut, populate the PriceCharged field. Reason is that at some point you may increase your price (and even do 'discount' jobs). Having a PriceCharged column lets you move your price around without getting confused about how much different customers owe....

Just a thought.

Mat

0

Select Grasscut.customerID,Date,Paid from Grasscut join customer on customer .id=Grasscut .id
where paid=false

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.