5> CREATE TABLE authors(
6> au_id varchar(11),
7> au_lname varchar(40) NOT NULL,
8> au_fname varchar(20) NOT NULL,
9> phone char(12) NOT NULL DEFAULT ('UNKNOWN'),
10> address varchar(40) NULL,
11> city varchar(20) NULL,
12> state char(2) NULL,
13> zip char(5) NULL,
14> contract bit NOT NULL
15> )
16> GO
1> insert authors values('1', 'Joe', 'Abra', '111 111-1111', '6 St.', 'Berkeley', 'CA', '11111', 1)
2> insert authors values('2', 'Jack', 'Majo', '222 222-2222', '3 St.', 'Oakland' , 'CA', '22222', 1)
3> insert authors values('3', 'Pink', 'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4', 'Blue', 'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5', 'Red', 'Anne', '555 555-5555', '6 Av.', 'Regina', 'SK', '55555', 1)
6> insert authors values('6', 'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina', 'SK', '66666', 1)
7> insert authors values('7', 'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8', 'Yellow','Heather','888 888-8888', '3 Pu', 'Vacaville', 'CA', '88888', 0)
9> insert authors values('9', 'Gold', 'Dep', '999 999-9999', '5 Av.', 'Oakland', 'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean', '000 000-0000', '4 Av.', 'Oakland', 'CA', '00000', 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE titleauthor(
4> au_id varchar(20),
5> title_id varchar(20),
6> au_ord tinyint NULL,
7> royaltyper int NULL
8> )
9> GO
1>
2> insert titleauthor values('1', '2', 1, 60)
3> insert titleauthor values('2', '3', 1, 100)
4> insert titleauthor values('3', '4', 1, 100)
5> insert titleauthor values('4', '5', 1, 100)
6> insert titleauthor values('5', '6', 1, 100)
7> insert titleauthor values('6', '7', 2, 40)
8> insert titleauthor values('7', '8', 1, 100)
9> insert titleauthor values('8', '9', 1, 100)
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> --Here's the query using IN:
4>
5> SELECT 'Author ID'=A.au_id,
6> 'Author'=CONVERT(varchar(20), RTRIM(au_lname) + ', '
7> + RTRIM(au_fname)), state
8> FROM authors A
9> WHERE A.au_id IN
10> (SELECT B.au_id FROM titleauthor B)
11> GO
Author ID Author state
----------- -------------------- -----
1 Joe, Abra CA
2 Jack, Majo CA
3 Pink, Cherry BC
4 Blue, Albert BC
5 Red, Anne SK
6 Black, Michel SK
7 White, Sylvia MD
8 Yellow, Heather CA
(8 rows affected)
1>
2>
3> --This is the query using equivalent formulation with = ANY:
4>
5> SELECT 'Author ID'=A.au_id,
6> 'Author'=CONVERT(varchar(20), RTRIM(au_lname) + ', '
7> + RTRIM(au_fname)), state
8> FROM authors A
9> WHERE A.au_id=ANY
10> (SELECT B.au_id FROM titleauthor B)
11> GO
Author ID Author state
----------- -------------------- -----
1 Joe, Abra CA
2 Jack, Majo CA
3 Pink, Cherry BC
4 Blue, Albert BC
5 Red, Anne SK
6 Black, Michel SK
7 White, Sylvia MD
8 Yellow, Heather CA
(8 rows affected)
1>
2>
3> drop table authors;
4> drop table titleauthor;
5> GO
1>
2>