CODEWITHSUNDEEP
sql
galdikas
galdikas

Reputation: 1669

SQL join with different "where" clause than the rest of the query?

    SELECT `bid_amount`,`valid_until`, min('bid_amount') as `lowest_bid`
    `cf1`.`country_name` AS `country_from`, `rq`.`city_from`,
    `cf2`.`country_name` AS `country_to`, `rq`.`city_to`,
    `sub_cat`.`sub_cat_name`,
    `cat`.`cat_name`
    FROM `bids`
    JOIN `request_quote` `rq` ON `bids`.`bid_for` = `rq`.`quoteid`
    JOIN `countries` `cf1` ON `rq`.`country_from` = `cf1`.`country_id`
    JOIN `countries` `cf2` ON `rq`.`country_to` = `cf2`.`country_id`
    LEFT JOIN `cat` ON `rq`.`cat` = `cat`.`cat_id`
    LEFT JOIN `sub_cat` ON `rq`.`sub_cat` = `sub_cat`.`sub_cat_id`
    WHERE `bids`.`bid_by` = $bidderId GROUP BY `bids`.`bid_amount`

So I am working on this auction kind of site. What I need to return is all bids by particular user, and in addition to that some extra information about every bid.

One piece of information that I want is the lowest bid on every item. So here is the bids table. 'bid_for` references the item for which the bid is. So I want to get that 'min(bid_amount)' that is referenced by "where bid_for = x". But as you see in the last line I already have WHERE clause that references it by user id.

So in short, is there anyway to select one (or more) piece of information that is referenced by different "where" clause than the rest of the query? (if that makes sense)

+--------+--------+---------+------------+-------------+---------------+-------------+
| bid_id | bid_by | bid_for | bid_amount | pickup_date | delivery_date | valid_until |
+--------+--------+---------+------------+-------------+---------------+-------------+
|      1 |     24 |       2 |      19.99 | 2013-06-01  | 2013-06-01    | 2013-06-01  |
|      2 |     27 |       2 |      25.00 | 2013-06-01  | 2013-06-01    | 2013-06-01  |
+--------+--------+---------+------------+-------------+---------------+-------------+

Upvotes: 1

Views: 99

Answers (2)

galdikas
galdikas

Reputation: 1669

Ok came up with solution myself. I aliased the bids table, and joined it to the un-aliased bids table. Like so:

SELECT `bids`.`bid_amount`,`bids`.`valid_until`,
    min(`min`.`bid_amount`) AS `lowest_bid`,
    `cf1`.`country_name` AS `country_from`, `rq`.`city_from`,
    `cf2`.`country_name` AS `country_to`, `rq`.`city_to`,
    `sub_cat`.`sub_cat_name`,
    `cat`.`cat_name`
    FROM `bids`
    JOIN `request_quote` `rq` ON `bids`.`bid_for` = `rq`.`quoteid`
    JOIN `bids` `min` ON `min`.`bid_for` = `rq`.`quoteid`
    JOIN `countries` `cf1` ON `rq`.`country_from` = `cf1`.`country_id`
    JOIN `countries` `cf2` ON `rq`.`country_to` = `cf2`.`country_id`
    LEFT JOIN `cat` ON `rq`.`cat` = `cat`.`cat_id`
    LEFT JOIN `sub_cat` ON `rq`.`sub_cat` = `sub_cat`.`sub_cat_id`
    WHERE `bids`.`bid_by` = $bidderId GROUP BY `bids`.`bid_for`

Didn't even know you could join same table onto same table. But here you go, live and learn :)

Upvotes: 0

bclemenzi
bclemenzi

Reputation: 144

You can do this with an inner select statement:

SELECT `bid_amount`,`valid_until`, (SELECT min('bid_amount') FROM 'bids') as `lowest_bid`
`cf1`.`country_name` AS `country_from`, `rq`.`city_from`,
`cf2`.`country_name` AS `country_to`, `rq`.`city_to`,
`sub_cat`.`sub_cat_name`,
`cat`.`cat_name`
FROM `bids`
JOIN `request_quote` `rq` ON `bids`.`bid_for` = `rq`.`quoteid`
JOIN `countries` `cf1` ON `rq`.`country_from` = `cf1`.`country_id`
JOIN `countries` `cf2` ON `rq`.`country_to` = `cf2`.`country_id`
LEFT JOIN `cat` ON `rq`.`cat` = `cat`.`cat_id`
LEFT JOIN `sub_cat` ON `rq`.`sub_cat` = `sub_cat`.`sub_cat_id`
WHERE `bids`.`bid_by` = $bidderId GROUP BY `bids`.`bid_amount`

Upvotes: 1

Related Questions