How can I set a value to '1' if certain values exist in a table and set a value to '0' otherwise?

I have a database named Books, which has a table named PrimaryBooks, which contains the following data:

Title            Pages  DateOfRelease
Sapiens          572    1987-01-20
Black Swan       852    2004-07-14
1984             429    1949-12-03
Influence        1582   2017-06-17
Freakonomics     826    2014-11-26
The Alchemist    371    2001-02-07
Quiet            639    2016-08-29
Blink            198    2022-04-11
The Art of War   2485   1772-09-10

The database also has another table called SECONDARYBOOKS, which contains the following data (They`re similar. However this table has two more columns, which are called COLOR and PRICE. The name of the other columns are the same as the ones in the first table, but in Caps, and while a lot of the data in the columns is the same as the other table, some values are slightly different):

COLOR    PRICE  TITLE           PAGES  DATEOFRELEASE
Green    17     Sapiens         834    1987-01-20
Black    25     Black Swan      852    2004-07-14
Gray     28     1984            429    1949-12-03
Orange   12     Influence       1582   2018-05-16
Red      31     Freakonomics    826    2014-11-26
White    37     The Alchemist   371    2001-02-07
Cyan     10     Meditations     1004   2016-08-29
Blue     18     Deep Work       624    2023-01-12
Yellow   29     The Art of War  2485   1672-09-10

What I want to do is to add a new column to the table SECONDARYBOOKS (Which must be called status), which will show either a 0 or a 1. It will show a 1 if the data in the row is exactly the same as the data in the first table (Except for COLOR and PRICE, since the first table doesn´t have that data), and it will show a 0 if it isn´t. This means that the table must end up looking like this:

COLOR    PRICE  TITLE           PAGES  DATEOFRELEASE  STATUS
Green    17     Sapiens         834    1987-01-20     0
Black    25     Black Swan      852    2004-07-14     1
Gray     28     1984            429    1949-12-03     1
Orange   12     Influence       1582   2018-05-16     0
Red      31     Freakonomics    826    2014-11-26     1
White    37     The Alchemist   371    2001-02-07     1
Cyan     10     Meditations     1004   2016-08-29     0
Blue     18     Deep Work       624    2023-01-12     0
Yellow   29     The Art of War  2485   1672-09-10     0

I would like to know how can I accomplish this.

I tried writing the following query:

SELECT CASE WHEN PB1.Title = SB.TITLE THEN
    CASE WHEN PB2.Pages = SB.PAGES THEN
        CASE WHEN SUBSTRING(CONVERT(varchar(15), PB3.DateOfRelease, 112), 1, 8) = SUBSTRING(CONVERT(varchar(15), SB.DATEOFRELEASE, 112), 1, 8) THEN
            '1'
        ELSE
            '0'
        END
    ELSE
        0'
    END
ELSE
    '0'
END
AS STATUS
FROM SECONDARYBOOKS SB
LEFT JOIN PrimaryBooks PB1 ON PB1.Title = SB.TITLE
LEFT JOIN PrimaryBooks PB2 ON PB2.Pages = SB.PAGES
LEFT JOIN PrimaryBooks PB3 ON PB3.DateOfRelease = SB.DATEOFRELEASE
GROUP BY SB.COLOR, SB.PRICE, SB.TITLE, SB.PAGES, SB.DATEOFRELEASE, PB1.Title, PB2.Pages, PB3.DateOfRelease

However, when I execute this query, it never stops trying to execute. It gets stuck.

Solution

I'm not sure why you were trying to do three separate joins. If that had executed, it would have effectively calculated "Some primary book has a matching title AND some primary book has a matching page count AND some primary book has a matching release date - not necessarily all the same book". I presume you want everything to match a single primary book.

I expect what you want is an EXISTS() test that is part of a CASE expression that sets the status flag to 1 or 0.

I am also unsure why you are doing date-to-string conversions before comparing what appear to already be date values. If the release date columns are already DATE types (or DATETIME type with 00:00:00 time components) there is no need for that conversion.

Assuming that you have already added the status column to the SECONDARYBOOKS table, you can use the following update.

UPDATE SB
SET status = CASE WHEN EXISTS(
        SELECT *
        FROM PrimaryBooks PB
        WHERE PB.Title = SB.TITLE
        AND PB.Pages = SB.PAGES
        AND PB.DateOfRelease = SB.DATEOFRELEASE
    ) THEN 1 ELSE 0 END
FROM SECONDARYBOOKS SB

Results:

COLOR	PRICE	TITLE	PAGES	DATEOFRELEASE	STATUS
Green	17	Sapiens	834	1987-01-20	0
Black	25	Black Swan	852	2004-07-14	1
Gray	28	1984	429	1949-12-03	1
Orange	12	Influence	1582	2018-05-16	0
Red	31	Freakonomics	826	2014-11-26	1
White	37	The Alchemist	371	2001-02-07	1
Cyan	10	Meditations	1004	2016-08-29	0
Blue	18	Deep Work	624	2023-01-12	0
Yellow	29	The Art of War	2485	1672-09-10	0

See this db<>fiddle for a demo.