Hi, i have three tables, my query involves these two tables:


Code:
CREATE TABLE instruments (
instrumentid int(6) NOT NULL auto_increment,
type varchar(25) NOT NULL default '',
model varchar(25) NOT NULL default '',
year char(4) NOT NULL default '',
place varchar(25) NOT NULL default '',
serialno varchar(25) NOT NULL default '',
condition varchar(50) NOT NULL default '',
description varchar(255) NOT NULL default '',
reserve int(6) NOT NULL default '',
enddate datetime NOT NULL default '0000-00-00 00:00:00',
userid int(6) NOT NULL,
archive varchar(4) NOT NULL default 'NO',
special varchar(4) NOT NULL default 'NO',
FULLTEXT (type, model, description),
PRIMARY KEY (instrumentid),
FOREIGN KEY (userid) references users(userid))ENGINE=MyISAM;

CREATE TABLE Bids (
bidid int(25) NOT NULL auto_increment,
bid int(6) NOT NULL default '',
date datetime NOT NULL default '0000-00-00 00:00:00',
userid int(6) NOT NULL,
instrumentid int(6) NOT NULL,
PRIMARY KEY (bidid),
FOREIGN KEY (userid) references users(userid),
FOREIGN KEY (instrumentid) references instruments(instrumentid))ENGINE=MyISAM;

I'd like to write a query that selects all instruments that have ended that day and displays a summary. So the table will show the instrumentid, type, reserve, userid(from instruments), userid(from bids), bid(highest bid from bids) and then two man made columns. One that displays 17.5% of the final price if the selling price is greater than the reserve, if not just displays n/a. The second that displays the selling price minus the 17.5% if the selling price is greater than the reserve, if not just displays n/a. If the reserve hasn't been met, i'd like the userid(from bids) to display n/a.



Help help would be great.

Many Thanks