#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    82
    Rep Power
    11

    Join 2 Select From 2 Tables


    I wanted to join 2 select stmt from 2 tables.

    TABLE A (3 rows)
    ID | NAME
    111 | AAA
    111 | BBB
    111 | CCC
    222 | III
    222 | JJJ
    333 | KKK

    TABLE B (4 rows)
    ID | LAST
    111 | EEE
    111 | FFF
    111 | GGG
    111 | HHH
    222 | PPP
    222 | QQQ
    222 | TTT
    333 | VVV
    333 | UUU

    I wanted to have a result like this:
    RESULT:

    ID | NAME | LAST
    111 AAA EEE
    111 BBB FFF
    111 CCC GGG
    111 NULL* HHH

    I tried the following query but got duplicate results.
    select A.name, B.last from A, B where a.id = 111 and b.id = 111

    Any ideas how to get the sample result above w/o duplicates?

    Thanks,
    Last edited by dikoB; December 11th, 2012 at 01:51 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    based on the data you posted, it's impossible, because there are no columns that can be used to identify which rows go with which rows

    try copy/paste using excel
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    82
    Rep Power
    11
    Originally Posted by r937
    based on the data you posted, it's impossible, because there are no columns that can be used to identify which rows go with which rows

    try copy/paste using excel
    Thanks. The tables are just representation though but I'm not sure if it's doable. Basically I just wanted to have an enumerated results from 2 tables side by side having same ID of 111. Notice the last row is NULL under "NAME" since TABLE A has only 3 rows.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    but ~all~ the rows have an id of 111

    how's it supposed to know that AAA goes with EEE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    82
    Rep Power
    11
    Originally Posted by r937
    but ~all~ the rows have an id of 111

    how's it supposed to know that AAA goes with EEE
    Okay thanks, it seems like there's no other way for this but to have a separate query on both tables
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    One query or two queries. It makes no difference. There's still no way of relating 'aaa' and 'eee'.

IMN logo majestic logo threadwatch logo seochat tools logo