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'
)
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
