来自2个表的SQL select语句 [英] SQL select statement from 2 tables

问题描述

我有一个小的SQL问题.

I have a small sql question.

我有2张桌子

会员和经理

成员具有:memberID，名称，地址 管理员具有:memberID，EditRights，DeleteRights

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

EditRights和DeleteRights的类型为bit.

EditRights and DeleteRights are of type bit.

魔鬼与会员有关系，因为他们本身就是会员.

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

我想选择所有成员的ID，姓名和地址，并为经理显示的成员显示他们是否具有编辑权限和/或删除权限.

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:

示例数据

成员:

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

经理:

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

我想显示这样的选择:

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

任何帮助都会很棒

推荐答案

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.

这篇关于来自2个表的SQL select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！