Query using IN and LIKE at the same time

The following query does not compile:
SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
OK, so it might it been a silly attempt, but seriously... I need to return
all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
including words like 'Rootbeer'. The reason why I am trying to use the IN is
because the ('%Beer%', '%apple%') will end up being replaced with a sub
query.
Thanks.SELECT * FROM Food AS f
WHERE EXISTS(
SELECT * FROM [sub_query_table] AS t
WHERE CHARINDEX(t.[field], f.[field]) > 0
)
Probably not very efficient, but will get what you want.
"Gecko" wrote:

> The following query does not compile:
> SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%')
> OK, so it might it been a silly attempt, but seriously... I need to return
> all the foods that have the words 'Beer' Or 'Apple' anywhere in the name
> including words like 'Rootbeer'. The reason why I am trying to use the IN
is
> because the ('%Beer%', '%apple%') will end up being replaced with a sub
> query.
> Thanks.
>
>|||There are several ways of doing this:
SELECT *
FROM Food
WHERE FoodName LIKE '%Beer%'
OR FoodName LIKE '%apple%' ;
-- or
SELECT *
FROM Food
INNER JOIN ( SELECT 'Beer' UNION
SELECT 'apple' ) D ( p )
ON FoodName LIKE '%' + p + '%' ;
Anith|||Thanks KH,
Just curios, would there be a way of doing the same things but instead of
returning a record if the record contains either '%Beer%' OR '%Apple%', I
would like to return the record only if it contains both '%Beer%' AND
'%Apple%' (notice how the first one is OR and the other AND)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
> SELECT * FROM Food AS f
> WHERE EXISTS(
> SELECT * FROM [sub_query_table] AS t
> WHERE CHARINDEX(t.[field], f.[field]) > 0
> )
> Probably not very efficient, but will get what you want.
>
> "Gecko" wrote:
>|||These might give you what you're looking for:
select * from Food as f
where 0 < all (
select charindex(t.[field],f.[field])
from subquerytable as t
)
or
select * from Food as f
where not exists (
select * from subquerytable
where charindex(t.[field],f.[field]) = 0
)
You might also look into SQL Server's full-text indexing.
Steve Kass
Drew University
Gecko wrote:

>Thanks KH,
>Just curios, would there be a way of doing the same things but instead of
>returning a record if the record contains either '%Beer%' OR '%Apple%', I
>would like to return the record only if it contains both '%Beer%' AND
>'%Apple%' (notice how the first one is OR and the other AND)
>
>"KH" <KH@.discussions.microsoft.com> wrote in message
>news:06AAF7A2-7878-49DF-AD46-B7A1837B5CF6@.microsoft.com...
>
>
>|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL:
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relatioanl division.