0

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.

milo2011
  • 339
  • 1
  • 9
  • 25
  • Not sure what you mean, the query appears to be working correctly for what's written; it returns rows where the column `City` and `Bookstore` both appear in the table `#AccessRights`. In your sample data, that's only 2 rows, `BookID`s `1` and `9`. If it was ignoring the 2nd join, you would get 6 rows, not 2. – Thom A Jul 29 '22 at 12:59
  • Obligatory note, as well, that SQL server 2008 has been *completely* unsupported for **3 years**; it is long past time you got that upgrade path finalised. – Thom A Jul 29 '22 at 13:04
  • It should return all the rows from Books where it finds 'Central1' and 'New York' in City OR Bookstore columns. – milo2011 Jul 29 '22 at 13:04
  • 1
    You don't have an effective `OR` you have an effective **`AND`**. If you want an `OR`, why not use one `JOIN` and an `OR`, or just use an `IN`? – Thom A Jul 29 '22 at 13:05
  • 1
    I know it may seem nitpicky, but the the words matter because they drive what you translate to code. You will never find a row that has 'Central1' **AND** 'New York' in City. Work on reframing your requirement and that will help as you build out your code. – Isaac Jul 29 '22 at 13:10
  • @Larnu, it has to be a temp table. – milo2011 Jul 29 '22 at 13:12
  • @Isaac, yes, bad choice of column name. – milo2011 Jul 29 '22 at 13:12
  • *"it has to be a temp table."* I'm not sure of your point, @milo2011 ? Why do you believe you can't use an `IN` or `OR` in a `JOIN` to a temporary table? – Thom A Jul 29 '22 at 13:14
  • 1
    Perhaps any confusion would be cleared up if you [Edit](https://stackoverflow.com/posts/73167123/edit) your question to include your expected results. – AlwaysLearning Jul 29 '22 at 13:15
  • 3
    What's wrong with `SELECT * FROM Books t1 JOIN #AccessRights t2 ON t2.val IN (t1.Bookstore, t1.City);` http://sqlfiddle.com/#!18/4bfaf9e/8 – Charlieface Jul 29 '22 at 13:57
  • @Charlieface is spot on. An equivalent and perhaps easier query to understand would be `SELECT * FROM Books t1 JOIN #AccessRights t2 ON t1.City = t2.val OR t1.Bookstore = t2.val` – Isaac Jul 29 '22 at 16:11
  • 1
    [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) [mre] PS Please clarify via edits, not comments. [ask] [Help] – philipxy Jul 29 '22 at 21:58

1 Answers1

0

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43