Water Cooler v2 Water Cooler v2 - 3 months ago 7
SQL Question

Select matching rows from a table where either one of two columns contain any value from a list of values

Could you please help me with a SQL query for the Microsoft SQL Server 2012 database? I have a table structure like so:

User
----------------------
Id int
UserName nvarchar


Likeable
---------------------
Id int
Name nvarchar


UserLike
----------------------
Id int
UserId int
LikeableId int


Book
-----------------------
Id int
Name nvarchar
Author nvarchar
Description nvarchar
AmazonUrl nvarchar


So, to get the names of things that a user likes, I use this query:

SELECT Name
FROM [User] u JOIN UserLike ul ON u.Id = ul.UserId
JOIN Likeable l ON l.Id = ul.LikeableId;


Let's call that query Query 1.


Results of Query 1:


Name
----------
Python
C#
Kotlin


What I want:

Now, I'd like to return a DISTINCT result set of books, i.e. all the columns of the Book table where either the book name or the book description contains any of the words from the results of Query 1. This is where my SQL skills come to halt.

I'd like to use this query in a Java
PreparedStatement
so if you could just stick to using a regular query rather than any fancy data structures or stored procedures, that'll be great.

I'll be using Microsoft SQL Server 2012 as the database.

Answer

You can do this with a JOIN . . . but using LIKE or CHARINDEX() for the condition:

SELECT b.*
FROM Book b JOIN
     (SELECT Name
      FROM [User] u JOIN
           UserLike ul
           ON u.Id = ul.UserId JOIN
           Likeable l 
          ON l.Id = ul.LikeableId
     ) n
     ON b.title LIKE '%' + n.name + '%' OR
        b.description LIKE '%' + n.name + '%';