Results 1 to 10 of 10

Thread: Table joining

  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Post Unanswered: Table joining

    Hi,
    Anyone can help me with this I have 2 table,
    say

    Table A
    ------------
    id
    ---
    1
    2
    3

    Table B.
    ---------------
    id name
    ------- ----------
    1 saj
    3 vnu
    4 dad
    1 saj
    I want result as follow

    id name
    -------------------
    1 saj
    2
    3 vnu

    ie, result should contain all elements of A, corresponding field of B if it exist, Eliminate duplicate rows
    Last edited by vishnusivanpillai; 01-09-12 at 02:01.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This seems to be rather simple task. What did you manage to do so far? Please, post your query.

  3. #3
    Join Date
    Jan 2012
    Posts
    5
    select id,name from table A, table B where table A.id=table b.id;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gocool View Post
    select id,name from table A, table B where table A.id=table b.id;
    nice try, but sorry, no cigar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by gocool View Post
    select id,name from table A, table B where table A.id=table b.id;
    Read up on outer joins

  6. #6
    Join Date
    Jan 2012
    Posts
    5
    Sorry guys, i am jus a beginner... Sorry fr the wrong info.. I thot i can give it a try...

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure, no problem. But you could have tested that query first. Doing so, you'd see that
    a) it is invalid (from a space in a table name to an ambiguously defined column)
    b) it produces wrong results

  8. #8
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by vishnusivanpillai View Post
    Hi,
    Anyone can help me with this I have 2 table,
    say

    Table A
    ------------
    id
    ---
    1
    2
    3

    Table B.
    ---------------
    id name
    ------- ----------
    1 saj
    3 vnu
    4 dad
    1 saj
    I want result as follow

    id name
    -------------------
    1 saj
    2
    3 vnu

    ie, result should contain all elements of A, corresponding field of B if it exist, Eliminate duplicate rows
    I don't understand nothing., post your query and see where is the error.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    read up on outer join and distinct. we will give clues but won't solve homework.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    You can get exactly same result by following both query, but read up on OUTER JOIN as all respected suggested.


    SQL> select a.id,nam from a,b
    2 where a.id=b.id(+)
    3 order by a.id;

    ID NAM
    ---------- -----
    1 abc
    2
    3 aabc


    SQL> select id,b1.nam from a a1 left outer join b b1 using (id) order by id;

    ID NAM
    ---------- -----
    1 abc
    2
    3 aabc

    keep it up.

Tags for this Thread

Posting Permissions

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