Hi Sebastian, Kevin,
Sebastian Mendel wrote:
Kevin Waterson schrieb:
I have a table of bookmarks that have keyword searching.
Fields
+-------------+----------------------+--------------------------+------------------+
| bookmark_id | bookmark_title | bookmark_url |
bookmark_keyword |
+-------------+----------------------+--------------------------+------------------+
| 1 | number one | http://www.phpro.org | php
|
| 2 | number 2 | http://www.freshmeat.net | software
|
| 3 | three here | http://www.php.net | php
|
| 4 | and four | http://www.redhat.com | linux
|
| 5 | the fifth | http://www.ez.no | php
|
| 6 | this will do | http://www.google.com | search
|
| 7 | something about fish | http://www.youtube.com | linux
|
| 8 | finally | http://www.redhat.com | php
|
+-------------+----------------------+--------------------------+------------------+
I wish to select all bookmark_title and bookmark_url that have the same
keywords as
the bookmark_url 'http://www.redhat.com. I do this..
mysql> SELECT child.bookmark_id, child.bookmark_title, child.bookmark_url FROM
bookmarks AS child join bookmarks AS parent ON parent.bookmark_keyword =
child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com' GROUP BY
child.bookmark_keyword;
But this returns..
+-------------+----------------+-----------------------+
| bookmark_id | bookmark_title | bookmark_url |
+-------------+----------------+-----------------------+
| 4 | and four | http://www.redhat.com |
| 8 | finally | http://www.redhat.com |
+-------------+----------------+-----------------------+
Where it should return also the results with the bookmark_id of 1 as the
bookmark_url
http://www.redhat.com has two keywords, "php" and "linux" so this should match
both.
What should I be looking at here?
at first you should make your queries readable if your request public help ...
SELECT
child.bookmark_id,
child.bookmark_title,
child.bookmark_url
FROM
bookmarks AS child
join
bookmarks AS parent
ON
parent.bookmark_keyword = child.bookmark_keyword
WHERE
child.bookmark_url='http://www.redhat.com'
GROUP BY
child.bookmark_keyword;
than i would suggest using subqueries:
SELECT
child.bookmark_id,
child.bookmark_title,
child.bookmark_url
FROM
bookmarks AS child
WHERE
child.bookmark_keyword IN (
SELECT
parent.bookmark_keyword
FROM
bookmarks AS parent
WHERE
parent.bookmark_url = 'http://www.redhat.com'
)
I would STRONGLY recommend AGAINST using subqueries in this manner.
MySQL optimizes this type of subquery very poorly, and I wouldn't even
"test" this query if the table is large -- it may kill the server. A
JOIN is the right approach here.
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
