Results 1 to 6 of 6

Thread: join

  1. #1
    Join Date
    Feb 2010
    Posts
    9

    Question Unanswered: join

    i want to join two columns of two different tables horizontly.
    ex.

    name is a column name of table t1
    name
    a
    b
    c

    address is a column name of table t2
    address
    x
    y
    z

    i want result llike this

    name address
    a x
    b y
    c z

    there is nothing common in both tables.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cannot be done
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, it can so long as it does not matter which address goes with which name....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Well, it can so long as it does not matter which address goes with which name....
    it might make an interesting thread to see how many ways we can come up with ...

    one derived table to generate ROW_NUMBER() over the names, another derived table to generate ROW_NUMBER() over the addresses, and a join based on the row numbers

    so lowest collating name goes with lowest collating address

    what happens if there aren't the same number of rows? need a FULL OUTER JOIN, i guess
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You could order your window function by NEWID() for that non-deterministic flavr.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    shantanu4u,

    I think you are not supplying us with enough information.

    Unless you are intending to write an application where you mail bills to customers using the random selection of an address from other customers, and you are really looking to gauge how pissed-off all your customers get when everyone gets the wrong bill . . .

    Or is this a design for a dating service where you randomly match people with other people, so you use a customer table randomly matched to an address table?

    Logic says that there has to be SOME relationship between your names table and your address table . . . unless my billing and dating guess are spot-on.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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