query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

query help

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post 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
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post 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.
Post Reply