CODEWITHSUNDEEP
mysqlsqlselectjoin
Mugur Ungureanu
Mugur Ungureanu

Reputation: 161

Select data from different columns

I have a table: BID

Id_auction        bidder_1      winner_1    bidder_2    winner_2    item
  1                Alice           1          Ben         1          cup
  2               Charles          0          Alice       1          mug
  3                 Ben            1          Charles     1          pen

If a bidder is a winner the value is 1 if is not a winner the value is 0. So, in first auction, Alice and Ben are winners, in the second only Alice wins. I will need a MySQL query so the results will be like this:

Id_auction       bidder        item      bidder_number
1                Alice         cup            1
1                Ben           cup            2
2                Alice         mug            2
3                Ben           pen            1
3                Charles       pen            2

Thank you!

Upvotes: 1

Views: 234

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

Try this:

SELECT *
FROM
(
  SELECT 
    t1.id_auction,
    t2.bidder_1 AS bidder,
    t2.item
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_1 = 1
  UNION ALL
  SELECT 
    t1.id_auction,
    t2.bidder_2 AS bidder,
    t2.item
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_2 = 1
) AS sub
ORDER BY id_auction;

SQL Fiddle Demo

This will give you:

| ID_AUCTION |  BIDDER | ITEM |
-------------------------------
|          1 |   Alice |  cup |
|          1 |     Ben |  cup |
|          2 |   Alice |  mug |
|          3 | Charles |  pen |
|          3 |     Ben |  pen |

Update 1

Just select a new column bidder_number with value 1 in the first query, and 2 in the second one like this:

SELECT *
FROM
(
  SELECT 
    t1.id_auction,
    t2.bidder_1 AS bidder,
    t2.item,
    1 AS bidder_number        -- <---------- This is the new column
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_1 = 1
  UNION ALL
  SELECT 
    t1.id_auction,
    t2.bidder_2 AS bidder,
    t2.item,
    2                         -- < --------- with value 2 for bidders 2
  FROM table1 t1
  INNER JOIN table1 t2  ON t1.id_auction = t2.id_auction
                       AND t2.winner_2 = 1
) AS sub
ORDER BY id_auction, bidder;

This will give you:

| ID_AUCTION |  BIDDER | ITEM | BIDDER_NUMBER |
-----------------------------------------------
|          1 |   Alice |  cup |             1 |
|          1 |     Ben |  cup |             2 |
|          2 |   Alice |  mug |             2 |
|          3 |     Ben |  pen |             1 |
|          3 | Charles |  pen |             2 |

SQL Fiddle Demo

Upvotes: 2

Related Questions