Re: bookmarks and keywords

From: Baron Schwartz <baron(at)xaprb.com>
Date: Mon Sep 24 2007 - 21:52:09 EDT

Kevin Waterson wrote:
> This one time, at band camp, Baron Schwartz <baron@xaprb.com> wrote:
>

>>> 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;
>>>
>> That query is right.

>

>>> But this returns..
>>> +-------------+----------------+-----------------------+
>>> | bookmark_id | bookmark_title | bookmark_url          |
>>> +-------------+----------------+-----------------------+
>>> |           4 | and four       | 
http://www.redhat.com |
>>> |           8 | finally        | 
http://www.redhat.com |
>>> +-------------+----------------+-----------------------+
>> But, the JOIN table from which those columns come actually has the 
>> columns you want from the parent table as well.  It's just that you're 
>> selecting the child columns.

>
> Im not sure I follow, the results should be those with id's of 1, 3, 4, 5, 6, 7, 8
>
> and If I chose http://www.php.net that has only the keyword of "php" then the results
> would be 1,3,5, and 8

Right. I misspoke slightly: the JOIN is right, but the SELECT list is not. Look again at your query:

SELECT child.bookmark_id, child.bookmark_title, child.bookmark_url ... WHERE child.bookmark_url='http://www.redhat.com'

Do you see why all your results have a bookmark_url of http://www.redhat .com? It's because you are selecting the columns from the *child* table and at the same time your WHERE clause guarantees the *child* table will ONLY have that URL. Keep looking at the WHERE clause and the select list until you see it. I'm highlighting it again below with -->ARROWS<--

SELECT ... -->CHILD<--.bookmark_url ...
... WHERE -->CHILD<--.bookmark_url ...

Do you see, child.bookmark_url is a *constant* in your query, in both the SELECT list and the WHERE clause?

The steps in the JOIN are conceptually like this:

1. find the rows in child that match the WHERE clause.
2. find matching rows in parent.
3. put them side-by-side.
4. choose columns from the result.
5. GROUP BY.

I'll abbreviate the table so it won't wrap and mess up the diagram I'm about to create.

create table bookmarks(bookmark_url varchar(50), bookmark_keyword varchar(50));

insert into bookmarks(bookmark_url, bookmark_keyword)   values

  ('
http://www.redhat.com', 'linux'),
  ('
http://www.redhat.com', 'php'),
  ('
http://www.ez.no', 'php'),
  ('
http://www.phpro.org', 'php'),
  ('
http://www.youtube.com', 'linux');

Here's a slightly altered query, essentially the same thing:

SELECT child.bookmark_url as cbu, child.bookmark_keyword as cbk,

        parent.bookmark_url as pbu, parent.bookmark_keyword as pbk FROM bookmarks AS child

    JOIN bookmarks AS parent

       ON parent.bookmark_keyword = child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com';

Imagine what the table looks like after step 3:

+-----------------------+-------+------------------------+-------+
| cbu                   | cbk   | pbu                    | pbk   |
+-----------------------+-------+------------------------+-------+
| 
http://www.redhat.com | linux | 
http://www.redhat.com  | linux |
| 
http://www.redhat.com | php   | 
http://www.redhat.com  | php   |
| 
http://www.redhat.com | php   | 
http://www.ez.no       | php   |

| http://www.redhat.com | php | http://www.phpro.org | php | | http://www.redhat.com | linux | http://www.youtube.com | linux |

+-----------------------+-------+------------------------+-------+

Do you see how the columns from the child are on the left, and the columns from the parent are on the right? Your WHERE clause holds the columns on the left constant, and that is what you are selecting from this intermediate table! You need to select from the right-hand columns:

SELECT parent.bookmark_url as pbu, parent.bookmark_keyword as pbk FROM bookmarks AS child

    JOIN bookmarks AS parent

       ON parent.bookmark_keyword = child.bookmark_keyword WHERE child.bookmark_url='http://www.redhat.com';

+------------------------+-------+
| pbu                    | pbk   |
+------------------------+-------+
| 
http://www.redhat.com  | linux |
| 
http://www.redhat.com  | php   |
| 
http://www.ez.no       | php   |

| http://www.phpro.org | php |
| http://www.youtube.com | linux |

+------------------------+-------+

I didn't change the JOIN a bit; I only changed which columns I'm extracting from this intermediate table.

You have one final problem, which isn't really causing you trouble with THIS query, but will likely bite you in the future: you are selecting non-grouped columns in a GROUP BY query. I'll refer you to something I wrote about that. You can read it at your leisure.

http://www.xaprb.com/blog/2006/03/11/many-to-one-problems-in-sql/

I hope this helps.

Baron

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com

Received on Mon Sep 24 21:53:10 2007