Re: Convert subquery

[Kristen G. Thorson]

Shawn,
Thanks so much, this is exactly what I was looking for.  I was having 
trouble getting the condition (page_links.page_id=6) in the right 
place.  I had gotten as far as

SELECT page.page_id, page_keyword
FROM page
LEFT JOIN page_links
ON page.page_id=page_links.child_id
WHERE page.page_id<>6
AND page_links.child_id is null;
Which of course didn't give me what I was looking for.  And of course it 
never occured to me I could put the missing condition in the ON clause.  
This one was a challenge for me, as I normally don't have to do anything 
beyond very simple queries.

Thanks again!
kgt

[EMAIL PROTECTED] wrote:
"Kristen G. Thorson" <[EMAIL PROTECTED]> wrote on 05/17/2005 
02:37:12 PM:

 

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 )
The table `page_links` contains "parent" (page_links.page_id) and 
"child" (page_links.child_id) mappings for items in `page`.  Given a 
page.page_id=6, I want all items in `page` that satisfy the following:

page.page_id<>6
and
page.page_id<>page_links.child_id only where page_links.page_id=6

So, for a given page, I want all possible child page candidates 
(condition is that current child pages and the given page are not 
possible candidates).

Thanks in advance!
kgt

   

Original:
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 )
JOINED:
SELECT page.page_id, page.page_keyword
FROM page
LEFT JOIN page_links
       on page.page_ID = page_links.child_id
       and page_links.page_id=6
WHERE page.page_id<>6
AND page_links.page_id is null
The trick to doing a NOT IN() query as a JOIN query is that you try to 
make an _optional_ match on your columns/conditions (LEFT JOIN...ON...) 
then look for those rows where the match-up conditions are *not* met 
(WHERE <table_on_the_right>.nonnullablecolumnname is NULL). I have assumed 
that in the table page_links, page_id is not a nullable column. So by 
checking for a null where there shouldn't be one, you detect where the 
matching conditions in your LEFT JOIN phrase weren't met. Does that make 
sense?

I know that talking in the negative can be confusing so here it is from 
the other direction.  A LEFT JOIN will make sure that all of the rows of 
the LEFT table  (the first table listed) are available to be processed 
against the conditions of the WHERE clause but only those rows from the 
table on the right-hand side of the phrase that meet the ON conditions 
will be in that same list. All column values (even those that are not 
normally nullable) for the table on the right side of a LEFT JOIN will be 
NULL if there is no match that meets the ON condition(s).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine