query help

[dizyn]

Hi

I have two database tables tables:
1. products: Fields: [proid, pro_name, price, selling_type]
2. bids: Fields: [buyerid,price,time,proid]

I have two type of products one with fix price and 2nd with bidding


Now:
Product 1: T Shirt is on fix price with price 150
Product 2: Cap is on auction which has starting price of 20 and there is no bid posted on it
Product 3: Pen is also on auction which has 2 bids on it

Now I want to see price for all the products and if the product in on auction i also want to see bids count?

Is there any way i can do it one query? OR I have to change my db? if so please also suggest.

thank you

[Fumigator]

I would suggest you put the price of the fixed-price products in the same table as the bid-price products, just define the price as fixed rather than bid. Then your "find product price" query would come from the same table regardless of what type of pricing the product uses.

[dizyn]

i tried this:
1. products: [proid, pro_name, selling_type]
2. bids: [buyerid,price,time,proid]

SQL:
SELECT * FROM products
INNER JOIN bids ON products.proid = bids.buyerid
GROUP BY products.proid
ORDER BY ID DESC

but it don't give me highest bid, any solution?

[guelphdad]

Go with Fumigator's solution.