MySQL problems with IN(SELECT

[Zych]

I keep running into problems when I want to run a statement with a IN(SELECT statement. Here is an example of what I would like to do:

INSERT INTO MN_032803_DD ( ID, FName, LName, Street, City, State, ZIP, Phone, HValue )
SELECT MN_032803.ID, MN_032803.FName, MN_032803.LName, MN_032803.Street, MN_032803.City, MN_032803.State, MN_032803.ZIP, MN_032803.Phone, MN_032803.HValue
FROM MN_032803
WHERE MN_032803.Phone NOT IN(SELECT DNC_MN.Phone FROM DNC_MN);

I get an error MySQL Front that says this:

You have an error in your SQL syntax near 'SELECT DNC_MN.Phone FROM DNC_MN)' at line 1

I have also tried to Link the tables into Access and use Access as the front end but I get an ODBC error basically saying the same thing.

Doesn't MySQL support this? Do I need to update something? I am using the last 3.x stable release. The person who set this up did not want to use version 4.x since it was new.

Thanks,

Zych

 

[sleipnir214]

Only MySQL 4.1 supports the kind of subselects you want to use, but that version is in alpha release.

This page:

http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

May be useful in helping you rewrite your query. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[swampBoogie]

INSERT INTO MN_032803_DD (
ID, FName, LName,
Street, City, State,
ZIP, Phone, HValue )
SELECT MN_032803.ID, MN_032803.FName, MN_032803.LName,
MN_032803.Street,MN_032803.City, MN_032803.State,
MN_032803.ZIP, MN_032803.Phone, MN_032803.HValue
FROM MN_032803 left outer join DNC_MN
ON MN_032803.Phone = DNC_MN.Phone
WHERE DNC_MN.Phone is null

 

[Zych]

Thanks for the reply guys. I think MySQL 4.1 has a final release now. I may try that.

swampBoogie - I don't fully understand all the Joins but I am trying to learn. What I am trying to do is get a result set from MN_032803 where there is no matches in DNC_MN. There is no Null's in DNC_MN. Is this statement correct or should I change the = to a <>?

Thanks,

Zych

 

[swampBoogie]

The statement is (intended to be) correct.

As I have an outer join all rows from the left table (MN_032803) is present in the result. For those records where there is no matching rows in the right table, the result will contain null for the columns originating from the right table. I.e. non matching rows will have a null in the DNC_MN.Phone column and the where clause will only select those rows.

 

[sleipnir214]

MySQL release 4.0.x is in release version.

As of this morning (US CDT), their site was classifying 4.1 as alpha software. And they're on of the few that actually go through alpha, beta, and gamma software before they release. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[Zych]

Thanks swampBoogie,

Worked like a charm. I guess I need to study up on my joins.

Thanks,

Zych