I am able to get what I want with one workaround but am unable to insert the
selected data into the target table - note below:
mysql> select h1.word, ' ', 0
-> from h1 left join h2 ah2 on h1.word = ah2.word
-> where ah2.word is NULL;
+-----------------------+---+---+
| word | | 0 |
+-----------------------+---+---+
| Products_and_Shopping | | 0 |
+-----------------------+---+---+
1 row in set (0.00 sec)
note - the above works but when I repeat above statement but prefix
the entire statement with "insert h2" - I get this error message -- any
ideas on how to fix this?
mysql> insert h2
-> select h1.word, ' ', 0
-> from h1 left join h2 ah2 on h1.word = ah2.word
-> where ah2.word is NULL;
ERROR 1093: INSERT TABLE 'h2' isn't allowed in FROM table list
At 12:53 PM 1/13/2002, Roleigh Martin wrote:
>Hello all, my first mysql posting. I've been a Sybase SQL Server
>programmer for
>10 years and the following select I know I have to translate as MySQL does not
>support nested selects.
>
>My version:
>mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu (i386)
>
>
>insert h2
>select w.word,' ', 0
>from words w, events e
>where e.word_id = w.word_id
>and e.leftseq = 1
>and 0 = (select count(*) from words w2, events e2
> where e2.url_id = e.url_id
> and e2.leftseq = 2
> and w2.word_id = e2.word_id )
>group by w.word;
>
>I've tried the following variants and they generate error messages:
>
>mysql> select w.word,' ',0
> -> from words w, events e, LEFT JOIN events e2 on ( e.url_id =
> e2.url_id and e2.leftseq = 2) -> where e.word_id = w.word_id
> -> and e.leftseq = 1
> -> and e2.url_id is NULL
> -> group by w.word;
>ERROR 1064: You have an error in your SQL syntax near 'LEFT JOIN events e2
>on ( e.url_id = e2.url_id and e2.leftseq = 2)
>where e.word_' at line 2
>
>mysql> insert h2
> -> select w.word,' ',0
> -> from words w, events e, LEFT JOIN events e2 on e.url_id = e2.url_id
> -> where e.word_id = w.word_id
> -> and e.leftseq = 1
> -> and e2.leftseq = 2
> -> and e2.url_id = NULL
> -> group by w.word;
>ERROR 1064: You have an error in your SQL syntax near 'LEFT JOIN events e2
>on e.url_id = e2.url_id
>where e.word_id = w.word_id
>and ' at line 3
>
>mysql> insert h2
> -> select w.word,' ',0
> -> from words w, events e, LEFT JOIN events e2 on e.url_id = e2.url_id
> -> where e.word_id = w.word_id
> -> and e.leftseq = 1
> -> and e2.leftseq = 2
> -> group by w.word;
>ERROR 1064: You have an error in your SQL syntax near 'LEFT JOIN events e2
>on e.url_id = e2.url_id
>where e.word_id = w.word_id
>and ' at line 3
>
>Appreciate your help in advance - thanks!
>
>
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
