--- David <[EMAIL PROTECTED]> wrote: > I have 3 tables which each contain a field named discount > > table1.discount > table2.discount > table3.discount > > I would like to do a join on the 3 tables and get the highest discount > value of the three where table1.item = 'some number' > > How should I structure the query? > > Thanks > > David
You could either create a temporary table (if you planned multiple queries against the data set) or use the UNION statement. SELECT discount FROM table1 WHERE item='some number' UNION SELECT discount FROM table2 WHERE item='some number' UNION SELECT discount FROM table2 WHERE item='some number' This may not make it easy for you to get the max() or min() value of discount. You could use the above to put data into the temporary table and query against it. You could apply the MAX() function to each query and then look at the three or N number of rows in the result to get a fairly quick result. In the end you'll have to decide how important it is to keep the data in separate tables as you describe. It'll be interesting to see other ideas inspired by this question. James Keeline
