question for select command

[snowsmart]

Here is command I run, and it return a ErrorNr 1064
As I am new to Mysql, please advice

select a.vendornumber from vendor as a
where a.vendornumber not in
(select b.vendornumber from po as b)

thanks

 

[sleipnir214]

You're using the "as" aliasing clause incorrectly.

You only use "as" when aliasing a columnname:

SELECT foo as bar from tablename


When you are aliasing a tablename, just follow the table's name with the alias:

SELECT a.foo from tablename a



Want the best answers? Ask the best questions! TANSTAAFL!

 

[snowsmart]

thanks for you quick response, the following script works in Mysql 4.1.15 but not Mysql 4.0.18 , as I can not update the Mysql, is there any way that I can make this query work in Mysql 4.0.18?

select a.vendornumber from vendor a
where a.vendornumber not in
(select b.vendornumber from po b)

thanks

 

[sleipnir214]

Subqueries do not work in older versions of MySQL. The MySQL online manual has an entry titled "Rewriting Subqueries as Joins for Earlier MySQL Versions", which will likely be of help.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[snowsmart]

You rock !

Thank you so much !

 

[r937]

sleip old buddy, there is nothing wrong with the keyword AS when aliasing a table

[tt]tbl_name [[AS] alias]
[{USE|IGNORE|FORCE} INDEX (key_list)][/tt]

http://dev.mysql.com/doc/refman/4.1/en/select.html

r937.com | rudy.ca

 

[sleipnir214]

Oops.

The real problem was the subquery and different MySQL versions anyway.



Want the best answers? Ask the best questions! TANSTAAFL!