SQL query only returns values for the first JOIN-CodePudding

I have a table named Books and a temp table named AccessRights with the following rows:

create table Books
(
  BookID int,
  Title varchar(150),
  PurchaseDate date,
  Bookstore varchar(150),
  City varchar(150)
);

INSERT INTO Books VALUES (1, 'Cujo', '2022-02-01', 'Central1', 'New York');
INSERT INTO Books VALUES (2, 'The Hotel New Hampshire', '2022-01-08', 'Central2', 'Las Vegas');
INSERT INTO Books VALUES (3, 'Gorky Park', '2022-05-19', 'Central2', 'New York');
INSERT INTO Books VALUES (4, 'Red Dragon', '2022-03-11', 'Central1', 'Las Vegas');
INSERT INTO Books VALUES (5, 'Chronicle of a Death Foretold', '2022-01-29', 'Central3', 'New York');
INSERT INTO Books VALUES (6, 'The Lord God Made Them All', '2022-04-04', 'Central2', 'New York');
INSERT INTO Books VALUES (7, 'A Light in the Attic', '2022-03-24', 'Central3', 'Las Vegas');
INSERT INTO Books VALUES (8, 'God Emperor of Dune', '2022-06-05', 'Central1', 'Las Vegas');
INSERT INTO Books VALUES (9, 'Tar Baby', '2022-01-15', 'Central1', 'New York');
INSERT INTO Books VALUES (10, ' Jumanji', '2022-02-13', 'Central2', 'New York');

CREATE TABLE #AccessRights
    (
        val varchar(50)NOT NULL
    );

INSERT INTO #AccessRights VALUES('Central1');
INSERT INTO #AccessRights VALUES('New York');

See fiddle.

When I run the script:

SELECT * FROM Books t1 
JOIN #AccessRights t2 ON t1.City  = t2.val
JOIN #AccessRights t3 ON t1.Bookstore  = t3.val;

It returns the values only for the first JOIN. What am I doing wrong?

I'm using MS SQL Server 2008.

CodePudding user response：

Looks like you can just use a single join with IN or OR

SELECT *
FROM Books t1
JOIN #AccessRights t2 ON t2.val IN (t1.Bookstore, t1.City);

SQL Fiddle