14

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.

Community
  • 1
  • 1
Nathan
  • 285
  • 1
  • 4
  • 10
  • 4
    I might not got the point, but why don't you use a left outer join? – Mithrandir Jan 03 '13 at 14:19
  • then the wrong products would be returned if the table is filled in – ufosnowcat Jan 03 '13 at 14:24
  • 1
    I think @Mithrandir is right: using a LEFT JOIN it will return all the record matching a provided "Synonym" and also the not matching record with join field NULL – Massimiliano Peluso Jan 03 '13 at 14:26
  • left join will give you all the records from products and if there is a record in synonyms it will give you info on that 2 or multiple rows. – ufosnowcat Jan 03 '13 at 14:31
  • Can you check the `@SynonymTable` before the query runs to see if there is a record, and if not, then insert `%` into it so the query will return everything? – James L. Jan 03 '13 at 14:32
  • @ufosnowcat: well, one might use distinct to supress that ... – Mithrandir Jan 03 '13 at 14:33
  • @Mithrandir The problem in doing that is that it is then returning duplicates rows from the [Products] table. – Nathan Jan 03 '13 at 14:34
  • @JamesL. I tried that earlier and for some reason it did not return anything at all. – Nathan Jan 03 '13 at 14:35
  • @Mithrandir disticnt will still get you wrong results ex prod table has aa bb syn table has %a% then select distinct * from prod left join syn on prod.Title LIKE syn.Synonym will give you 2 decords 1) aa %a% and 2) bb null – ufosnowcat Jan 03 '13 at 14:39
  • You can use a LEFT JOIN in this case with IS NOT NULL OR NOT EXISTS ... -> it should do exactly what you need – András Ottó Jan 03 '13 at 14:50

3 Answers3

7

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)
András Ottó
  • 7,605
  • 1
  • 28
  • 38
4

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
rs.
  • 26,707
  • 12
  • 68
  • 90
1

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 ))
rs.
  • 26,707
  • 12
  • 68
  • 90
ufosnowcat
  • 558
  • 2
  • 13