Join Problem

Nura · #1 (**permalink**) 10-01-05, 07:32

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.

r937 · #2 (**permalink**) 10-01-05, 08:05

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

Nura · #3 (**permalink**) 10-03-05, 00:13

It did work thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech