tuff query

[djc]

tblTest:
ID
Keyword

Sample data:
100, chicken
100, chicken soup
100, soup
101, turkey soup
101, african turkey
102, cooking soup
102, cooking
103, african soup

Is it possible to create a query that can return only the IDs that have user
supplied keywords associated with them?

Example1: users enters 'soup' as a single keyword. My code would take that
and make it *soup* to get all with 'soup' anywhere in it. And it should
return 100, 101, and 102.

Example2: user enters two keywords. 'soup' and 'turkey'. I make them *soup*
and *turkey*. It should return ONLY 101. I want to use a logical AND
condition. The ID must have BOTH keywords associated with it but it they
don't have to be in the same record. For example, this same query should
return only 101 even if 'soup' and 'turkey' were two SEPERATE records, like
this: 101, soup and 101, turkey.

Example3: user enters two keywords. 'africa' and 'soup'. I change to
*africa* and *soup*. Query should return ONLY 103. Not the others with
'soup' and not the other with 'african'. There must be a record with both
keywords in it together, OR 2 seperate records each with one of the
keywords.

anyone know how I could accomplish this? I very good method was posted by
John Viescas but it didn't support wild cards. You can look for the post
subject "Keyword lookup system guidance needed" to see that. I started this
new post because John's help has got me to a more narrowly defined
'question'. So I started fresh.

any help is greatly appreciated.

 

[Chris2]

See Below:

tblTest:
ID
Keyword

Sample data:
100, chicken
100, chicken soup
100, soup
101, turkey soup
101, african turkey
102, cooking soup
102, cooking
103, african soup

CREATE TABLE MiscSoupStuff
(MiscSoupStuffID INTEGER
,MiscID INTEGER
,SoupType CHAR(72)
,CONSTRAINT pk_MiscStoupStuff PRIMARY KEY (MiscSoupStuffID)
)

Sample data:

1, 100, chicken
2, 100, chicken soup
3, 100, soup
4, 101, turkey soup
5, 101, african turkey
6, 102, cooking soup
7, 102, cooking
8, 103, african soup

Is it possible to create a query that can return only the IDs that have user
supplied keywords associated with them?

Example1: users enters 'soup' as a single keyword. My code would take that
and make it *soup* to get all with 'soup' anywhere in it. And it should
return 100, 101, and 102.

SELECT M1.MiscID
,M1.SoupType
From MiscSoupStuff AS M1
WHERE M1.SoupType LIKE "*soup*";

Except this returns MiscID values 100, 101, 102, and 103, because 103 also
has "soup" in it.

Example2: user enters two keywords. 'soup' and 'turkey'. I make them *soup*
and *turkey*. It should return ONLY 101. I want to use a logical AND
condition. The ID must have BOTH keywords associated with it but it they
don't have to be in the same record. For example, this same query should
return only 101 even if 'soup' and 'turkey' were two SEPERATE records, like
this: 101, soup and 101, turkey.

SELECT M1.MiscID
,M1.SoupType
From MiscSoupStuff AS M1
WHERE M1.SoupType LIKE "*soup*"
AND M1.SoupType LIKE "*turkey*";

This returns only MiscID value 101.

Example3: user enters two keywords. 'africa' and 'soup'. I change to
*africa* and *soup*. Query should return ONLY 103. Not the others with
'soup' and not the other with 'african'. There must be a record with both
keywords in it together, OR 2 seperate records each with one of the
keywords.

SELECT M1.MiscID
,M1.SoupType
From MiscSoupStuff AS M1
WHERE M1.SoupType LIKE "*soup*"
AND M1.SoupType LIKE "*africa*";

This returns only MiscID value 103.

 

[djc]

I may be missing something here (probably am) but I don't know what the new
table is for. But going just on the queries there is still the AND issue.
(agian, unless the table you have here changes something that I don't
understand.) Your query examples still have the problem of not being able to
return the desired results for an ID with more than one keyword used in an
AND condition when the keywords are in seperate records. The AND condition
is tested against 1 record at a time and it is impossible for it to ever be
TRUE when one keyword is in one record and the other keyword being tested is
in a seperate record. Its always FALSE. thats the issue I'm having. I do
appreciate your reply though. Please let me know If I am just
misunderstanding your reply though.

thanks.

 

[Chris2]

It's not really a "new" table. However, if I'm going to test out my
suggestions to you, I need a table with some data in it to operate on. The
Create Table Query and the sample data I loaded into are shown here so you
know what I was doing. You personally can duplicate my efforts with the
information I've provided.

More Below:

I may be missing something here (probably am) but I don't know what the new
table is for. But going just on the queries there is still the AND issue.
(agian, unless the table you have here changes something that I don't
understand.) Your query examples still have the problem of not being able to
return the desired results for an ID with more than one keyword used in an
AND condition when the keywords are in seperate records. The AND condition
is tested against 1 record at a time and it is impossible for it to ever be
TRUE when one keyword is in one record and the other keyword being tested is
in a seperate record. Its always FALSE. thats the issue I'm having. I do

Hmmm. You're correct. I didn't go into the issue of relational database
design, and standard methods and best practices in structuring tables and
storing data in them because, unless those issues are a specific part of the
questions, for me to trot out here and start lecturing (which I've done
before <sadly>) is usually beyond the scope of what the questioner is
looking for.

One and only one keyword should be stored per record. This prevents
duplication of data in separate records, conforms to First Normal Form (also
known as 1NF) rules (no data item that can be divided into sub-items may be
stored in a column, only indivisible data should be stored; or put another
way, "Data must be Atomic"), and will also solve your search problem.

The table would look something like this:

<same as before, but I've change the PRIMARY KEY and am putting MiscID data
now in MiscSoupStuffID; there is undoubtedly a FOREIGN KEY of MiscSoupStuff
to another table; the column names, of course, should receive better names>

CREATE TABLE MiscSoupStuff
(MiscSoupStuffID INTEGER
,MiscID INTEGER
,SoupType CHAR(72)
,CONSTRAINT pk_MiscStoupStuff PRIMARY KEY (MiscSoupStuffID, MiscID)
)

Sample data:

100, 1, chicken
100, 2, chicken
100, 2, soup
100, 3, soup
101, 4, turkey
101, 4, soup
101, 5, african
101, 5, turkey
102, 6, cooking
102, 6, soup
102, 7, cooking
103, 8, african
103, 8, soup