Re: Convert subquery

[Kristen G. Thorson]

I actually had something similar to this at one point, but I never got 
it working.  Your query returned 148 rows, which I can tell you right 
away can't be right since there are only 130 items in `page`.  I believe 
the problem is that it doesn't take into account that a page may be a 
child page of several different parents.  Therefore this part:

SELECT page.page_id, page_keyword
FROM page 
LEFT JOIN page_links    
   ON page.page_id=page_links.child_id

Will return more than 130 rows:
mysql> SELECT page.page_id, page_keyword, page_links.page_id FROM page 
LEFT JOIN page_links ON page.page_id=page_links.child_id;

+--------------+--------------+--------------------+
| page.page_id | page_keyword | page_links.page_id |
+--------------+--------------+--------------------+
|            5 | training     |                  4 |
|            5 | training     |                  6 |
|            5 | training     |                 17 |
|            4 | services     |                  6 |
|            4 | services     |                 12 |
|            4 | services     |                 13 |
|            4 | services     |                 14 |
|            4 | services     |                 15 |
Then adding the condition ( page_links.page_id <> 6 OR 
page_links.child_id IS NULL ) would still leave duplicate rows:

+--------------+--------------+--------------------+
| page.page_id | page_keyword | page_links.page_id |
+--------------+--------------+--------------------+
|            5 | training     |                  4 |
|            5 | training     |                 17 |
|            4 | services     |                 12 |
|            4 | services     |                 13 |
|            4 | services     |                 14 |
|            4 | services     |                 15 |
Shawn has already posted a working answer, so this is just me sharing 
how proud I am that I figured it out well enough to know why it doesn't 
work, which I guess is as good a learning tool as any!

Thanks,
kgt

Jan Pieter Kunst wrote:
2005/5/17, Kristen G. Thorson <[EMAIL PROTECTED]>:
 

I am moving some code that was developed against MySQL 4.1 to a server
with 3.23, and I have one query that uses a subquery.  I'm having a bear
of a time wrapping my mind around how to convert this.  The original
query is this:
SELECT page.page_id, page.page_keyword
FROM page
WHERE page.page_id<>6
AND page.page_id
NOT IN (
   SELECT page_links.child_id
   FROM page_links
   WHERE page_links.page_id=6 )
   

With some minimal test data, this seems to work, at first sight:
SELECT page.page_id
FROM page 
LEFT JOIN page_links ON page_links.child_id=page.page_id
WHERE (page_links.page_id <> 6 OR page_links.page_id IS NULL)
AND page.page_id <> 6

JP
.