"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
