Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: 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 me

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    Hi, can you post some sample data with expected outcome?

    Madhivanan

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think he'd want a FULL OUTER JOIN on this in case data is not present for one of the two options:

    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    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
    A retainer fee is a common occurance where there is an invoice without a quote or even any service/time/labor.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I got me one o' them retainers fer my overbite back when'st I was in high-school. Derned uncomfertable.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    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.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zobernjik
    ...but there is too much to explain.
    i know that feeling
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    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?
    Yeah, 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •