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?

Recommended Answers

All 3 Replies

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

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.