CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Water Cooler v2
Water Cooler v2

Reputation: 33850

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.

Upvotes: 0

Views: 874

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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 + '%';

Upvotes: 2

Related Questions