BEGIN TRAN -- Transaction will be rolled back so as to avoid
-- permanent changes
-- Dummy authors row
INSERT authors
(au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
VALUES
('000-00-0000', '***No Current Author***',
'', 'NONE', 'NONE', 'NONE', 'XX', '99999', 0)
-- Dummy titles row
INSERT titles
(title_id, title, type, pub_id, price, advance, royalty, ytd_sales,
notes, pubdate)
VALUES
('ZZ9999', '***No Current Title***',
'NONE', '9999', 0.00, 0, 0, 0, 'NONE', '1900.01.01')
-- Associate authors with no current titles to dummy title
INSERT titleauthor VALUES ('341-22-1782', 'ZZ9999', 0, 0)
INSERT titleauthor VALUES ('527-72-3246', 'ZZ9999', 0, 0)
INSERT titleauthor VALUES ('724-08-9931', 'ZZ9999', 0, 0)
INSERT titleauthor VALUES ('893-72-1158', 'ZZ9999', 0, 0)
-- Associate titles with no current author to dummy author
INSERT titleauthor VALUES ('000-00-0000', 'MC3026', 0, 0)
-- Now do a standard INNER JOIN
SELECT
'Author'=RTRIM(au_lname) + ', ' + au_fname,
'Title'=title
FROM
authors AS A -- JOIN conditions
INNER JOIN titleauthor AS TA ON A.au_id=TA.au_id
INNER JOIN titles AS T ON t.title_id=TA.title_id
WHERE
A.state <> 'CA'
ORDER BY 1
ROLLBACK TRAN -- Undo changes
