Thread: Mysql query
View Single Post
Old 2005-03-23, 06:06 AM   #15
raymor
The only guys who wear Hawaiian shirts are gay guys and big fat party animals
 
Join Date: Jan 2004
Posts: 178
Send a message via ICQ to raymor
Wow yall went through some contortions
on a pretty straightforward query.
It's called a self join.
Oh, and BTW, like is for pattern matching.
You want to use = when looking for
exact matches.
The query is kind of long just because the question itself
is long, with several parts, but it's very simple.
To understand how it works, let's first just
see how to get sponsors with either horizontal or vertical:


Code:
SELECT  sponsors.sponsor 
FROM sponsors, sponsors AS sponsors_h 
WHERE sponsors.keyword='vertical' AND
sponsors_h.keyword='horizontal' AND
sponsors.sponsor=sponsors_h.sponsor
Throw a "distinct" in there if you have a silly RDMS
that doesn't assume distinct by default.
So we've seen how to do a self join, that is
how to join a table to itself, which is just
another case of relational multiplication (cartesian product) like any other.
Now we just need to multiply by a third relation
to get the "button or small" part:

Code:
SELECT  sponsors.sponsor 
FROM sponsors, sponsors AS sponsors_h,
sponsors_b
WHERE sponsors.keyword='vertical' AND
sponsors_h.keyword='horizontal' AND
(
   sponsors_b.keyword='small' OR
   sponsors_b.keyword='button'
) AND
sponsors.sponsor=sponsors_h.sponsor AND
sponsors_b.sponsor=sponsors.sponsor
As always, the above code is off the top of my head,
untested
and my contain typos or logical errors.
(Fully tested code requires that I test it on your system.)
__________________
Ray Morris
support@bettercgi.com
Strongbox/Throttlebox & more
TXDPS #A14012
raymor is offline   Reply With Quote