今天写了几个SQL语句。觉得应该记下来

环境：SQL　SERVER2005个人版，在最后多表查询时搞了好久，想想这个东东以后可能有用。先记下！
sql 代码

create database test;

use test;

DROP TABLE IF EXISTS addresses;

/*地址表*/
create table addresses(
id int primary key identity,
address varchar(20) not null,/*地址*/
zipcode int/*邮编*/
);

DROP TABLE IF EXISTS  family;


/*家庭表*/
create table family(
id int unique identity not null,
name varchar(20),   /*家长名*/
address_id int  not null,　/*地址ID*/
primary key(id) ,
constraint family_address foreign key(address_id) references addresses(id)
);

DROP TABLE IF EXISTS person;
/*个人信息表*/
create table person(
id int primary key identity,
family_id int  not null,　/*家庭ID*/
com_address_id int  not null,/*公司地址ID*/
name varchar(20) not null,/*姓名*/
sex varchar(5)not null,/*性别*/
constraint person_family_address foreign key(family_id) references family(id),
constraint person_complay foreign key(com_address_id) references  addresses(id)
)


/*查看个人家庭信息*/

select person.name as '姓名', person.sex as '性别',family.name as '家长名',
addresses.address as '家庭住址' ,B.address as '公司地址'
from person,addresses,family,
(select addresses.id,addresses.address from person,addresses
where addresses.id=person.com_address_id)B
where
    addresses.id=family.address_id  and family.id=person.family_id
    and person.com_address_id=B.id;