Kevin Waterson wrote:
This one time, at band camp, Baron Schwartz <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
