0

I have 2 table member table and voucher table. Member has many voucher, voucher has manay member

For example:

  • Member A has voucher1, voucher2
  • Voucher1 has members memberA, membersB, memberC.
  • Voucher2 has members memberA, memberC
  • memberB has vouchers voucher1
  • MemberC has vouchers voucher1, voucher2, voucher3
  • Vocher3 has members memberC, memberD
  • MemberD has voucher4
  • Voucher4 has members memberD

How create query to get all relation member has voucher from memberA to member D related by voucher

Thanks

GMB
  • 216,147
  • 25
  • 84
  • 135
El Rusdi
  • 11
  • 3
  • 4
    please provide sample data and expected output. Also show your attempt please – Squirrel Dec 09 '19 at 00:26
  • Try using CTE read this [LINK](https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example) for reference. – Buchiman Dec 09 '19 at 00:31

2 Answers2

1

This is too long for a comment.

To properly represent the many-to-many relationship between members and vouchers, you do need a third table, which is usually called a junction table. Using such a table would vastly simplified your design, hence your queries.

Consider the following design:

members
    member_id -- primary key
    -- other columns: member name, email, address...

vouchers
    voucher_id -- primary key
    -- other columns

members_vouchers  --> junction table
    member_id    -- foreign key to members(member_id)
    voucher_id   -- foreign key to voucher(voucher_id)
    primary key(member_id, voucher_id)

Now you can easily generate a query that lists all members and their associated vouchers, like:

select m.*, v.*
from members m
inner join members_vouchers mv on mv.member_id = m.member_id
inner join vouchers v on v.voucher_id = mv.voucher_id
GMB
  • 216,147
  • 25
  • 84
  • 135
0
SELECT V.voucher, M.member FROM VOUCHER AS V
LEFT OUTER JOIN MEMBER AS M 
ON M.voucher=V.voucher
GROUP BY M.member

It is not clear what you have in the tables. Can you at least provide two row output of each table?

It is not clear what you expect the output to be. Can you provide what you would expect good output to look like?

GMB
  • 216,147
  • 25
  • 84
  • 135
SwSci.Org
  • 41
  • 1
  • 7