AND Operator in Keyword Search Query

[Guest]

I'm having trouble with something that I think should be easy but just can't
seem to grasp...

I have a table of items. I also have a table of keywords that are
associated with items in the item table through the use of a composite table
that has a primary key of a foreign key from both the Items and Keyword
table, similar to this:

Items Table Keyword Table Composite
Table
Item_ID Item Keyword_ID Keyword Item_ID
Keyword_ID
1 Baseball 1 Baseball 1
1
2 Ball
1 2


My question is this:

How can I query the items table based on a keyword search using and AND
operator: i.e. How can I find items that have Keyword 1 (Baseball) AND
Keyword 2 (Ball) associated with them?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

How about (untested):

SELECT I.Item, K.Keyword
FROM ([Items Table] As I INNER JOIN [Composite Table] As C
ON I.Item_ID=C.Item_ID) INNER JOIN [Keyword Table] As K
ON C.Keyword_ID=K.Keyword_ID
WHERE K.Keyword='Ball'
AND K.Keyword='BaseBall'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkCPEYechKqOuFEgEQILdwCfW57DTONPlEhSuf8HCFy70bM6MWkAnA0Y
ZtGOoX2YjMbGD0Hx5RusAMPG
=wbte
-----END PGP SIGNATURE-----

 

[Guest]

Unfortunately, simply using the AND operator in this circumstance does not
return the desired results. What I am really after is all Items that have
'Ball' AND 'Baseball' as a key word. From the sample data, this means that
Item 1 - Baseball should be returned but if there were other items such as
football that also was associated with keyword 'Ball' would not.

The suggested structure returns no results because those two conditions are
never true simultaneously.

I have also tried using a Union query of duplicate queries each with one
part of the condition (I.E. ...WHERE Keyword = 'Baseball' UNION ...WHERE
Keyword = 'Ball')
which returns results closer to what is needed but that also includes
records where either of the conditions is true not where only both of them
are.

It almost seems as though I need to accomplish an INTERSECT query...I'll do
a search here to see if that's been discussed and if there is further input
to this thread, it will be greatly appreciated!

Thanks for your help, MG!

How about (untested):

SELECT I.Item, K.Keyword
FROM ([Items Table] As I INNER JOIN [Composite Table] As C
ON I.Item_ID=C.Item_ID) INNER JOIN [Keyword Table] As K
ON C.Keyword_ID=K.Keyword_ID
WHERE K.Keyword='Ball'
AND K.Keyword='BaseBall'

darrep wrote:
I'm having trouble with something that I think should be easy but just can't
seem to grasp...

I have a table of items. I also have a table of keywords that are
associated with items in the item table through the use of a composite table
that has a primary key of a foreign key from both the Items and Keyword
table, similar to this:

Items Table Keyword Table Composite
Table
Item_ID Item Keyword_ID Keyword Item_ID
Keyword_ID
1 Baseball 1 Baseball 1
1
2 Ball
1 2


My question is this:

How can I query the items table based on a keyword search using and AND
operator: i.e. How can I find items that have Keyword 1 (Baseball) AND
Keyword 2 (Ball) associated with them?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this (not thoroughly tested):

SELECT Item
FROM [Item Table]
WHERE Item_id IN (SELECT C.item_id
FROM [Composite table] AS C
INNER JOIN [Keyword table] As K
ON C.keyword_id = K.keyword_id
WHERE K.Keyword in ('ball', 'baseball')
GROUP BY c.item_id
HAVING count(*) = 2)

You'll have to know the number of keywords you are going to use and put
that number in the HAVING clause:

HAVING Count(*) = ? <- put your number in place of the ?.

The above subquery is explained in the _Journal of Information Systems
Education, Vol. 13(2)_. I found it on the internet doing a search for
Relational Division.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHQQYechKqOuFEgEQIIpACeNY4pOjz2Jlgn9Jy2TTjV5SJP4w0Amwd8
V7LdZxImdeAr5zhsTng9mLK4
=euUE
-----END PGP SIGNATURE-----

Unfortunately, simply using the AND operator in this circumstance does not
return the desired results. What I am really after is all Items that have
'Ball' AND 'Baseball' as a key word. From the sample data, this means that
Item 1 - Baseball should be returned but if there were other items such as
football that also was associated with keyword 'Ball' would not.

The suggested structure returns no results because those two conditions are
never true simultaneously.

I have also tried using a Union query of duplicate queries each with one
part of the condition (I.E. ...WHERE Keyword = 'Baseball' UNION ...WHERE
Keyword = 'Ball')
which returns results closer to what is needed but that also includes
records where either of the conditions is true not where only both of them
are.

It almost seems as though I need to accomplish an INTERSECT query...I'll do
a search here to see if that's been discussed and if there is further input
to this thread, it will be greatly appreciated!

Thanks for your help, MG!

How about (untested):

SELECT I.Item, K.Keyword
FROM ([Items Table] As I INNER JOIN [Composite Table] As C
ON I.Item_ID=C.Item_ID) INNER JOIN [Keyword Table] As K
ON C.Keyword_ID=K.Keyword_ID
WHERE K.Keyword='Ball'
AND K.Keyword='BaseBall'


darrep wrote:
I'm having trouble with something that I think should be easy but just can't
seem to grasp...

I have a table of items. I also have a table of keywords that are
associated with items in the item table through the use of a composite table
that has a primary key of a foreign key from both the Items and Keyword
table, similar to this:

Items Table Keyword Table Composite
Table
Item_ID Item Keyword_ID Keyword Item_ID
Keyword_ID
1 Baseball 1 Baseball 1
1
2 Ball
1 2


My question is this:

How can I query the items table based on a keyword search using and AND
operator: i.e. How can I find items that have Keyword 1 (Baseball) AND
Keyword 2 (Ball) associated with them?