Join tables help

[croiva25]

Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.

What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.

But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.

I hope this makes sense and somebody can help me

 

[r937]

select t1.option
     , t1.AirFares as AirFareQuoted
     , t2.AirFares as AirFareInvoiced
  from yourtable as t1
left outer
  join yourtable as t2
    on t1.option = t2.option
   and t2.ID = -1
 where t1.ID = 1

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SonOfEmidec1100]

Bit confused but this may help, if not can you give an example of a record and the result you expect.

select AirFareQuoted = CASE ID WHEN 1 THEN AirFares ELSE 0  END, 
       AirFareInvoiced  = CASE ID WHEN 1 THEN 0 ELSE AirFares  END 
FROM MyTable