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
query help
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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]
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
- itsmani1
- Forum Regular
- Posts: 791
- Joined: Mon Sep 29, 2003 2:26 am
- Location: Islamabad Pakistan
- Contact:
I noticed above query is not giving you highest bid as well.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