Page 1 of 1

query help

Posted: Thu Nov 15, 2007 8:45 am
by itsmani1
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

Posted: Thu Nov 15, 2007 11:23 am
by John Cartwright
Remove the price from the products table, and put the price of non-biddable items in the bids row (and treat it like a bid). It will be up to you to differentiate between biddable items and priced items using the selling_type

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

Code: Select all

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

Posted: Fri Nov 16, 2007 5:30 am
by itsmani1
thank you, you really solved my problem.

one more question, in this can i find how many bids has been posted, i mean bids count?

thank you

Posted: Fri Nov 16, 2007 5:54 am
by itsmani1
Jcart wrote:Remove the price from the products table, and put the price of non-biddable items in the bids row (and treat it like a bid). It will be up to you to differentiate between biddable items and priced items using the selling_type

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

Code: Select all

SELECT * FROM products
INNER JOIN bids ON products.proid = bids.buyerid
GROUP BY products.proid
ORDER BY ID DESC
I noticed above query is not giving you highest bid as well.