Re: bookmarks and keywords
Baron Schwartz schrieb: 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'; [..] no, you didn't, you just switched the names ... :-/ but from his original point of view the row with the given URL was his parent and he wants to have all childs (identified by the same keywords) - now you have just switched the names - making the childs his parents and the parents his childs - this is confusing just tell him: first the table which 'filters': FROM parent WHERE ... than the table he wants to select from: SELECT ... FROM child and now join them on the identifying field: SELECT child FROM parent JOIN child USING (keyword) WHERE parent... -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
Baron Schwartz schrieb: 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. SELECT DISTINCT will help too, of course (at least in similar cases) only if required (instead of GROUP BY) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bookmarks and keywords
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? Kind regards kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
Hi Kevin, Kevin Waterson wrote: 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; 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. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
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]
Re: bookmarks and keywords
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]
Re: bookmarks and keywords
Baron Schwartz schrieb: Hi Kevin, Kevin Waterson wrote: 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; That query is right. no, it is not shouldn't it read: SELECT child.bookmark_id, child.bookmark_title, child.bookmark_url FROM bookmarks AS parent join bookmarks AS child ON parent.bookmark_keyword = child.bookmark_keyword WHERE parent.bookmark_url = 'http://www.redhat.com' GROUP BY child.bookmark_id; ??? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
Baron Schwartz schrieb: 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. i don't think so -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
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 Kind regards Kevin -- Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookmarks and keywords
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
