WHERE IN Question

I have a couple of Tables already supplied in the form

MainRecord

Record ID  Details  Textual Information  
    1      AAAAAAA  ... some text referring to Oxford St Giles...  
    2      BBBBBBB  ... some text referring Oxford....  
    3      CCCCCCC  ... some text referring to Oxford St Aldate...  

and supporting table

PlaceNames

Record ID   PlaceName
    1        Oxford  
    1        St  
    1        Giles  
    2        Oxford  
    3        Oxford  
    3        St  
    3        Aldate  

I want to be able to build a search term so that if I can enter all or partial terms on place names. E.g. if I enter 'Oxford' I get all 3 records, if I enter 'Oxford' and 'Giles' I only get record 1 - almost like using a WHERE IN ('Oxford','Giles') but the terms are ANDed instead of being ORed?

I don't know if I can achieve this? I have tried various sub-queries without success

I am using SQL Server 2008

I wanted to avoid making a fulltext search field

Any pointers to clear the mist would be very helpful.

* Main Record detail updated to avoid confusion *

The only link between the 2 tables is the record ID

** Updated ** Nov 03 with sample tables

CREATE TABLE MAIN_RECORD (RecordID int,DocumentRef varchar(100));  
INSERT INTO MAIN_RECORD VALUES (86, 'Doc Referring to William Samuel ADAMS');  
INSERT INTO MAIN_RECORD VALUES (87, 'Doc Referring to William JONES');  
INSERT INTO MAIN_RECORD VALUES (88, 'Doc Referring to Samuel SMITH');  



CREATE TABLE FORENAMES (RecordID int,Forename varchar(25));  
INSERT INTO FORENAMES VALUES (86, 'William');  
INSERT INTO FORENAMES VALUES (86, 'Samuel');  
INSERT INTO FORENAMES VALUES (87, 'William');  
INSERT INTO FORENAMES VALUES (88, 'Samuel');  

My intial query is

SELECT main.[RecordID],main.documentRef  
    FROM [MAIN_RECORD] main  
    INNER JOIN  [FORENAMES] fn  
    ON main.RecordID = fn.RecordID  
    WHERE fn.ForeName IN ('William')  

This is fine and returns

RecordID    documentRef  
86  Doc Referring to William Samuel ADAMS  
87  Doc Referring to William JONES  

ditto with Samuel, etc

my problem is when I have more than 1 entry in the forename Search field i.e.

SELECT main.[RecordID],main.documentRef  
    FROM [MAIN_RECORD] main  
    INNER JOIN  [FORENAMES] fn  
    ON main.RecordID = fn.RecordID  
    WHERE fn.ForeName IN ('William,Samuel') 

This returns nothing.

I need this to ONLY return the MAIN record that has BOTH Samuel AND William in it, ie. when the search term has more than one name in it.

It also need to find William Samuel as well as Samuel William.

From posts by others, I have gone down the route of DIVISION and come up with the following (putting some string manipulation before main SELECT ):

DECLARE @Forename nvarchar(max)
DECLARE @SQLCommand nvarchar(max)
DECLARE @Number_of_Terms int
SET @Forename = 'William,Samuel'
--SET @Forename = 'Samuel,William'
--SET @Forename = 'William'
--SET @Forename = 'Samuel'
SET @Number_of_Terms = LEN(@Forename) - LEN(REPLACE(@Forename,',',''))+1
SET @Forename = REPLACE(@Forename,',',''',''') 

SET @SQLCommand = 'SELE开发者_Go百科CT fr.RecordID FROM dbo.BRS109_FullRecord fr '+
'INNER JOIN dbo.BRS109_Forenames fn '+
'ON fr.RecordID = fn.RecordID '+
'WHERE fr.RecordID = fn.RecordID '+
'AND fn.forename IN ('''+@Forename +''') ' +
' GROUP BY fr.RecordID ' +
' HAVING COUNT(fr.RecordId) = ' + CAST(@Number_of_Terms AS varchar(2)) +
' ORDER BY fr.RecordId'

EXECUTE sp_executesql @SQLCommand

This seems to give me what I am looking for.

Many thanks to all for contributing especially 'Quassnoi' and 'onedaywhen' - very helpful

SELECT  *
FROM    mainrecord mr
WHERE   (
        SELECT  COUNT(*)
        FROM    placenames pn
        WHERE   pn.record = mr.record
                AND pn.placename IN ('Oxford', 'St', 'Giles')
        ) = 3

Are you alluding to relational division? e.g. the supplier who supplies all products, the pilot that can fly all the planes in the hanger, etc?

If so, this article has many example implementations in SQL.

Here's one using your data:

WITH MainRecord (Record_ID, Details, Textual_Information)
     AS
     (
      SELECT Record_ID, Details, Textual_Information
        FROM (
              VALUES (1, 'AAAAAAA', ' ... some text referring to Oxford St Giles... '), 
                     (2, 'BBBBBBB', ' ... some text referring Oxford.... '), 
                     (3, 'CCCCCCC', '  ... some text referring to Oxford St Aldate... ')
             ) AS MainRecord (Record_ID, Details, Textual_Information)
     ), 
     PlaceNames (Record_ID, PlaceName)
     AS
     (
      SELECT Record_ID, PlaceName
        FROM (
              VALUES (1, 'Oxford'), 
                     (1, 'St'), 
                     (1, 'Giles'), 
                     (2, 'Oxford'), 
                     (3, 'Oxford'), 
                     (3, 'St'), 
                     (3, 'Aldate')
             ) AS PlaceNames (Record_ID, PlaceName)
     ),
     FullSet (Record_ID, PlaceName, Textual_Information)
     AS
     (
      SELECT DISTINCT P1.Record_ID, P1.PlaceName, 
             M1.Textual_Information 
        FROM MainRecord AS M1
             CROSS JOIN PlaceNames AS P1
     ), 
     NoMatch (Record_ID, PlaceName, Textual_Information)
     AS 
     (
      SELECT F1.Record_ID, F1.PlaceName, F1.Textual_Information
        FROM FullSet AS F1
              EXCEPT
      SELECT P1.Record_ID, P1.PlaceName, 
             M1.Textual_Information 
        FROM MainRecord AS M1
             INNER JOIN PlaceNames AS P1
               ON M1.Textual_Information LIKE '%' + P1.PlaceName + '%'
      )
SELECT F1.Record_ID, F1.PlaceName, F1.Textual_Information
  FROM FullSet AS F1
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM NoMatch AS N1
                     WHERE N1.Record_ID = F1.Record_ID
                           AND N1.Textual_Information = F1.Textual_Information
                   );

UPDATE:

I preferred your original data ;) No matter, my suggested approach is the same i.e. relational division (this time with better joins):

WITH FullSet (RecordID, Forename, DocumentRef)
     AS
     (
      SELECT DISTINCT P1.RecordID, P1.Forename, 
             M1.DocumentRef 
        FROM MAIN_RECORD AS M1
             INNER JOIN FORENAMES AS P1
                ON M1.RecordID = P1.RecordID
     ), 
     NoMatch (RecordID, Forename, DocumentRef)
     AS 
     (
      SELECT F1.RecordID, F1.Forename, F1.DocumentRef
        FROM FullSet AS F1
              EXCEPT
      SELECT P1.RecordID, P1.Forename, 
             M1.DocumentRef 
        FROM MAIN_RECORD AS M1
             INNER JOIN FORENAMES AS P1
               ON M1.RecordID = P1.RecordID
                  AND M1.DocumentRef LIKE '%' + P1.Forename + '%'
      )
SELECT F1.RecordID, F1.Forename, F1.DocumentRef
  FROM FullSet AS F1
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM NoMatch AS N1
                     WHERE N1.RecordID = F1.RecordID
                           AND N1.DocumentRef = F1.DocumentRef
                   );

not exactly sure in your environment, but in Oracle, this one should work.

select * from mainrecord
where placename like '%Oxford%'
INTERSECT
select * from mainrecord
where placename like '%Giles%'

Use LIKE statement like so:

SELECT * 
FROM table AS t 
WHERE t.PlaceName LIKE "%Oxford%" AND t.PlaceName LIKE "%Giles%"

Using this query you won't need the second table, everything will be done through the first one

If that's all you need, you can use LIKE

SELECT PlaceName FROM PlaceNames WHERE PlaceName LIKE "%Oxford%" AND PlaceName LIKE "%Giles%"

If you need a bit more flexibility (e.g. limiting matching to whole words), you can add Regex search to SQL Server easily

    SELECT PlaceName FROM PlaceNames WHERE dbo.RegExMatch(PlaceName,'\bOxford\b') = 1 
AND dbo.RegExMatch(PlaceName,'\bGiles\b') = 1