SQL select statement from 2 tables

Question

Hi, I have a small sql question.

I have 2 tables

Members and Managers

Members has: memberID, Name, Address Managers has: memberID, EditRights, DeleteRights

EditRights and DeleteRights are of type bit.

Mangers have a relationship with Members, because they are members themselves.

I want to select all members id's, name and adress and for the members that are managers show if they have editrights and/or deleterights.

SO:

Exmaple data

Members:

ID, Name, Address
1, tom, 2 flat
2, dan, 3 flat
3, ben, 4 flat
4, bob, 6 flat
5, sam, 9 flat

Managers:

ID, Editrights, deleterights 
2, 0, 1
4, 1, 1
5, 0, 0

I would like to display a select like this:

1, tom, 2 flat, no rights
2, dan, 3 flat, Delete
3, ben, 4 flat, no rights
4, bob, 6 flat, Edit&Delete
5, sam, 9 flat, no rights

Any help would be great

Answer 1

What you want is a left join

Answer 2

SELECT * FROM members LEFT OUTER JOIN managers ON member.id = manager.id

I wouldn't recommend concatenating the rights columns into strings like "no rights" - that's something better left to the presentation side of your application. Return as much data as possible in its native form so you can more easily work with it later.

Answer 3

I think this is closer to what you're asking for. The OUTER term is what gets you members that aren't in the Managers table. The CASE.. + stuff might need some tweaking to work with whatever DB you're using, but you get the idea - @ABach is correct about handling this in your presentation code if you can.

SELECT m.ID, m.Name, m.Address,
CASE WHERE g.EditRights IS NULL AND g.deleterights IS NULL THEN 'no rights'
ELSE
  CASE WHERE g.Editrights = 1 THEN 'Edit' END
  + CASE WHERE g.Editrights = 1 AND g.deleterights = 1 THEN '&' END
  + CASE WHERE g.deleterights = 1 THEN 'Delete' END
END AS rights
FROM Members AS m
LEFT OUTER JOIN Managers AS g ON g.ID = m.ID