Results 1 to 3 of 3

Thread: Join Problem

  1. #1
    Join Date
    Oct 2005
    Posts
    21

    Unanswered: Join Problem

    HI
    I have got 3 tables
    Member_master(mem_id,mem_name)
    Member_address(mem_id,mem_add1,mem_add2)
    Member_relatives(mem_id,rel_name,rel_add1,rel_add2 )

    A member has multiple rows in Member_address and multiple rows in Member_relatives.The problem is that the address values are repeated.
    i used join but there was repetion of similar rows.
    How can i retrieve data from these tables without the repetion of the address.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have two unrelated (unrelated to each other) one-to-many relationships

    if you do a three-table join, then of course you will get "duplicates"

    the only way that you can retrieve data from both one-to-many relationships at the same time in the same query is with a UNION, like this --
    Code:
    select M.mem_id
         , M.mem_name
         , 'member address: ' as address_type
         , A.mem_add1         as address_1
         , A.mem_add2         as address_2
      from Member_master as M
    inner
      join Member_address as A
        on M.mem_id = A.mem_id
    union all
    select M.mem_id
         , M.mem_name
         , 'relative address: ' 
         , R.rel_add1
         , R.rel_add2
      from Member_master as M
    inner
      join Member_relatives as R
        on M.mem_id = R.mem_id
    note it's UNION ALL, not UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2005
    Posts
    21
    It did work thanks

Posting Permissions

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