Thanks. Maybe I should be more specific about what I'm trying to do: the three tables are
inventory categories departments each of these has a discount field of which I want to find the highest value to apply to an items price Only the inventory field has the item_number field so I'm looking for inventory.item_number = 'some number' The common fields that will be matched are: inventory.category and categories.category inventory.department and departments.department I tried this with just 2 of the tables figuring if I could get that to work it wouldn't be hard to add the 3rd criteria. $item_query = "select inventory.discount, categories.discount from inventory left join categories on lower(inventory.category) = lower(categories.category) and lower(inventory.sub_category) = lower(categories.sub_category) where inventory.item_number = '99467BCD' ; " ; When I run this and look at the row there is only one discount value showing. Is there a way to get both discounts to show in the row? Then I could compare their values. David --- In [email protected], James Keeline <[EMAIL PROTECTED]> wrote: > > --- 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 >
