Here is a quick solution, should help get you passed this hurdle. It demonstrates that you can use INNER JOIN and retain the full details of the set by using simple CASE conditionals. The code is somewhat verbose in order to be more self explanatory.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* Simplified schema ****************************
+---------------+ +---------+
| BOOK | | LOAN |
+---------------+ +---------+
| isbn |-|-----------0<| isbn |
| Title | | dateout |
| yearpublished | | cardno |
+---------------+ +---------+
*************************************************/
IF OBJECT_ID('dbo.BOOK') IS NOT NULL
BEGIN
BEGIN TRY
ALTER TABLE dbo.LOAN DROP CONSTRAINT FK_DBO_LOAN_DBO_BOOK_ISBN;
END TRY
BEGIN CATCH
PRINT 'OOPS';
END CATCH
DROP TABLE dbo.BOOK;
END
CREATE TABLE dbo.BOOK
(
isbn VARCHAR(20) NOT NULL CONSTRAINT PK_DBO_BOOK_ISBN
PRIMARY KEY CLUSTERED
,Title VARCHAR(50) NOT NULL
,yearpublished DATE NOT NULL
);
IF OBJECT_ID('dbo.LOAN') IS NOT NULL DROP TABLE dbo.LOAN;
CREATE TABLE dbo.LOAN
(
cardno VARCHAR(15) NOT NULL
,isbn VARCHAR(20) NOT NULL CONSTRAINT FK_DBO_LOAN_DBO_BOOK_ISBN
FOREIGN KEY REFERENCES dbo.BOOK ( isbn )
,dateout DATE NOT NULL
,CONSTRAINT PK_DBO_LOAN_CARDNO_ISBN_DATEOUT PRIMARY KEY CLUSTERED
(cardno,isbn,dateout)
);
INSERT INTO dbo.BOOK
(
isbn
,Title
,yearpublished
) VALUES
('123-456-78-001','The Book of Topic 001','1910-01-01')
,('123-456-78-002','The Book of Topic 002','1911-02-01')
,('123-456-78-003','The Book of Topic 003','1919-03-01')
,('123-456-78-004','The Book of Topic 004','1920-04-01')
,('123-456-78-005','The Book of Topic 005','1921-05-01')
,('123-456-78-006','The Book of Topic 006','2001-06-01')
,('123-456-78-007','The Book of Topic 007','2002-07-01')
,('123-456-78-008','The Book of Topic 008','2003-08-01')
,('123-456-78-009','The Book of Topic 009','2004-09-01')
,('123-456-78-010','The Book of Topic 010','2005-10-01');
INSERT INTO dbo.LOAN
(
cardno
,isbn
,dateout
) VALUES
('9440-18400','123-456-78-001','2012-01-15')
,('9440-18400','123-456-78-002','2012-02-15')
,('9440-18400','123-456-78-003','2012-03-15')
,('9440-18400','123-456-78-004','2012-04-15')
,('9440-18400','123-456-78-005','2012-05-15')
,('9440-18400','123-456-78-006','2012-06-15')
,('9440-18400','123-456-78-007','2012-07-15')
,('9440-18400','123-456-78-008','2012-08-15')
,('9440-18400','123-456-78-009','2012-09-15')
,('9440-18400','123-456-78-010','2012-10-15')
,('1193-18404','123-456-78-006','2002-12-31')
,('1193-18404','123-456-78-007','2003-12-31')
,('1193-18404','123-456-78-008','2004-12-31')
,('1193-18404','123-456-78-009','2005-12-31')
,('1193-18404','123-456-78-010','2006-12-31')
,('3939-18414','123-456-78-003','2012-07-01')
,('3939-18414','123-456-78-004','2013-06-01')
,('3939-18414','123-456-78-005','2014-05-01')
,('8258-18447','123-456-78-004','2012-07-01')
,('8258-18447','123-456-78-006','2013-06-01')
,('8258-18447','123-456-78-008','2014-05-01')
,('8258-18447','123-456-78-010','2014-09-01')
,('4977-18459','123-456-78-008','2003-01-01')
,('8050-18463','123-456-78-003','2003-01-05');
;WITH LOAN_BOOK_BASE AS
(
SELECT
L.cardno
,L.isbn
,L.dateout
,CASE
WHEN L.dateout < '2003-01-03' THEN 1
ELSE 0
END AS LDO_20030103
,B.yearpublished
,CASE
WHEN YEAR(B.yearpublished) < 1920 THEN 1
ELSE 0
END AS PUB_1920
,B.Title
FROM LOAN L
INNER JOIN BOOK B
ON L.isbn = B.isbn
)
,FILTERED_SET AS
(
SELECT
LBB.cardno
,ROW_NUMBER() OVER
(
PARTITION BY LBB.cardno
ORDER BY LBB.dateout
) AS LBB_RID
,LBB.isbn
,LBB.dateout
,LBB.LDO_20030103
,LBB.yearpublished
,LBB.PUB_1920
,LBB.Title
FROM LOAN_BOOK_BASE LBB
WHERE LBB.LDO_20030103 = 1
OR LBB.PUB_1920 = 1
)
SELECT
FS.cardno
,FS.isbn
,FS.dateout
,FS.LDO_20030103
,FS.yearpublished
,FS.PUB_1920
,FS.Title
FROM FILTERED_SET FS
WHERE FS.LBB_RID = 1;
Results
cardno isbn dateout LDO_20030103 yearpublished PUB_1920 Title
--------------- -------------------- ---------- ------------ ------------- ----------- ----------------------
1193-18404 123-456-78-006 2002-12-31 1 2001-06-01 0 The Book of Topic 006
3939-18414 123-456-78-003 2012-07-01 0 1919-03-01 1 The Book of Topic 003
4977-18459 123-456-78-008 2003-01-01 1 2003-08-01 0 The Book of Topic 008
8050-18463 123-456-78-003 2003-01-05 0 1919-03-01 1 The Book of Topic 003
9440-18400 123-456-78-001 2012-01-15 0 1910-01-01 1 The Book of Topic 001