CODEWITHSUNDEEP
sql-servert-sqlinner-join
Nathan
Nathan

Reputation: 285

Ignore SQL INNER JOIN if there are no records to join?

I have the following Join

INNER JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])

The @SynonymTable table variable contains (if needed) a list of items terms such as:

%shirt%
%blouse%
%petticoat%

These are all based on a list of a synonyms for a particular keyword search, such as the term 'shirt' - from this I can then find all items that may be related, etc. The problem is that if the there is no keyword supplied the query obviously does not join anything.

Is there anyway to eliminate the join or return all items if there are no items in the synonym table?

I've found posts such as Bypass last INNER JOIN in query but am unable to get it to work for my scenario?

Any help or advice would be great.

Upvotes: 14

Views: 23545

Answers (3)

András Ottó
András Ottó

Reputation: 7695

You can use one select like this:

SELECT * FROM Products 
LEFT JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])
WHERE A.[Synonym] IS NOT NULL 
      OR NOT EXISTS (SELECT B.[Synonym] FROM @SynonymTable B)

Upvotes: 7

ufosnowcat
ufosnowcat

Reputation: 558

a solution would be to not join on the synonym table but to use it in a where clause

not the most elegant code but should work (unless you have a big synonym table then it gets slower)

where ((select count(1) from @SynonymTable) = 0 or 
       (select count(1) from @SynonymTable 
        where [Products].[Title] LIKE @SynonymTable.[Synonym]) > 0 ))

Upvotes: 1

rs.
rs.

Reputation: 27427

Use two different queries, check if synonymtable has rows and run query with inner join else return rows from products table

IF EXISTS (SELECT * FROM @SynonymTable)
BEGIN
  SELECT * FROM Products 
  INNER JOIN @SynonymTable AS A ON ([Products].[Title] LIKE A.[Synonym])
END
ELSE
  SELECT * FROM Products

Upvotes: 4

Related Questions