Search code examples
mysqlsqldatabaseinnodb

SELECT same data from same table just with conditions as different columns

I think it is best to show you he query first:

    SELECT 
    Positive.Amount AS PosAmount, 
    Negative.Amount AS NegAmount, 
    booking.Correction
FROM (booking)
LEFT JOIN ( SELECT ID, Amount FROM booking WHERE Amount < 0 ) 
    AS Negative ON booking.ID = Negative.ID
LEFT JOIN ( SELECT ID, Amount FROM booking WHERE Amount > 0 ) 
    AS Positive ON booking.ID = Positive.ID

What I am trying to achieve here is the following. I want the amount of each booking depending if it is positive or negative in a different column. Is that possible in another, much cheaper way? I mean joining a table to itself can not be the best way, can it?

The table looks like:

enter image description here

And the desired result is like this:

enter image description here

Thanks in advance!

Solution

  • You just need to use a CASE statement here. No need for subqueries and self-joins:

    SELECT
    CASE WHEN amount > 0 THEN amount END as posAmount,
    CASE WHEN amount < 0 THEN amount END as negAmount,
    correction
FROM booking