Join tables help

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 meHi, can you post some sample data with expected outcome?

Madhivanan|||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|||I think he'd want a FULL OUTER JOIN on this in case data is not present for one of the two options:

select coalesce(t1.option, t2.option) as option,
t1.AirFares as AirFareQuoted
t2.AirFares as AirFareInvoiced
from yourtable as t1
full outer join yourtable as t2
on t1.option = t2.option
where nullif(t1.ID, 1) = 1
and nullif(t2.ID, -1) = -1|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoice|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!|||blindman, he didn't say so, but my business logic is that you can't create an invoice unless there was a quote, but you can have a quote without it leading to an invoiceA retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.

-PatP|||Don't talk to me about LOGIC, man! We're dealing with BUSINESS!

You ivory tower Canadians with your high-falutin "educations" and your "logic" are just out of touch with the way things are done down here in the Red States! We don't got no truck with that elitist stuff no more!You are starting to frighten me... Have you thought about a career in politics?

-PatP|||A retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.whare kin ah git me one a them?

that sounds lahk jest the ticket fer my consultin bidness

i wanna be retained!!|||I got me one o' them retainers fer my overbite back when'st I was in high-school. Derned uncomfertable.|||You guys have just about lost your collective minds today...

Did someone spike the watercooler?

Post the DDL for the table...sounds like a normalization problem to me...|||Thanks guys for your replys, I will use this and see what I get.

I was going to post the view I am trying to create but there is too much to explain.|||...but there is too much to explain.i know that feeling|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :D|||I just love Lindman's obsession with coalesce, especially when there is no 3rd value...I guess trying to be cute is a very strong feeling...Trying to compensate for something? :DYeah, but I'm rather fond of standard, portable ways like Coalesce() to do things too... I hate having to rewrite code every time I switch database engines again.

-PatP|||exactamundo

i shudder every time i see someone use NVL and ISNULL

it's not an obsession, rdjabarov, it's just smart coding

why, what do you use instead of coalesce?

:)|||I don't get all lit up about IsNull, Nvl, and other vendor specific extensions to SQL. Often those are the things that force the standards committee to act on a much needed construct for the standard instead of debating it until half past doomsday. If a vendor specific extension is the only practical way from where I'm at to where I want to be, I'll gladly use the extension, but if there's a standard way to get there, I'll prefer it almost every time!

-PatP|||Well, maybe it's because COALESCE is really misleading

BOL:

COALESCE
Returns the first nonnull expression among its arguments.

The actual Def

Definition: [v] mix together different elements; "The colors blend well"; "fuse the clutter of detail into a rich narrative"--A. Schlesinger
[v] fuse or cause to grow together

In DB2 it's COALESCE and VALUE(Col1,'Argument')

Oracle is NVL as well...

OK...I take it back...NVL, VALUE, ISNULL are totally separate functions..COALESCE Can accept MANY Values and the first one that is NOT NULL, or the absence of anything, wins.

So they do seem to be different...

babble..babble..bable...

lack of sleep..sorry|||It's true, I am obsessed with COALESCE. I dream about it every night, and it coalesce(haunts, infests) my every waking thought. I feel the coalesce(compulsion, desire, need) to use it coalesce(constantly, continually, incessantly).

WHY COALESCE()?!!! WHY! WHY!!!

Somebody please coalesce(help, aid, assist) me!

You crack me up rdjabarov. But happy nullif(christmas, hannukah) to you anyway. :)|||happy nullif(christmas, hannukah) to you anywaymy nomination for quote of the year

nice one, blindman

:)|||My apologies to all the Kwanzaa and Festivus celebrators out there, but NULLIF could only take two values... :)|||actually, you should really leave NULL as the last option, for those of us without any religious or societal holiday adherence|||Unnecessary. If both parameters are null, NULLIF() returns null...|||... 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.
I hope this makes sense and somebody can help me

Maybe its much less confusing to you if you decide not to work with the table itself, but with two views separating your quotes and spents?! In practise:

CREATE VIEW Quotes AS SELECT * FROM <YourTable> WHERE ID = 1;
CREATE VIEW Spents AS SELECT * FROM <YourTable> WHERE ID = -1;

If there is a way to relate quotes to spends, you can join these two views using this relation in any (INNER, OUTER, FULL OUTER) way you want.

I hope this helps.