Select query always empty when using where

[jamgog]

I have imported my data ok, as when I run the query

select * from tablename;

all the records are displayed ok. However if I try the query

select * from tablename where columnname = "condition";

I get the message empty set, even though I can see the condition from the first query.

Any suggestions

 

[Tim1]

Hiya,

Could you give a specific example of where this is happening.

Tim

 

[jamgog]

sure,

table name = aircraft

fields
aircraft_id varchar(10)
manufacturer varchar(50)
model varchar(50)
mark varchar(20)

a query of select * from aircraft; returns=

|"air693" | "Vickers Supermarine" | "Spitfire" | "LF IX B"
|"air695" | "Yakovlev" | Yak-11" |

if then query
select aircraft_id from aircraft where model = "Spitfire";

I get the message "Empty Set"

Any ideas ?? (its probably something simple)

 

[Tim1]

Hi,

To be honest, not a clue. There doesn't appear to be anything that is causing this.

Do you have an index on that column? If so, it may be worth running UPDATE STATISTICS aircraft, just in case the index is corrupted.

If not, try:

select aircraft_id from aircraft where SUBSTRING(model,1,8) = "Spitfire";

There is no logical reason why that should work any better, but could be worth a try.

Tim

 

[DumTech]

I've barely begun to play with MySQL, so I'm not sure how strings are treated... Could there be extra characters (eg \n) in the field? Wonder if trying to select where model LIKE '%Spitfire%' would give you the result you expect?

Just a thought...

 

[jamgog]

The LIKE command works fine, it was just SELECT WHERE that didn't. All sorted now though:

It seems to be something with the way the textfile was imported. I initially did it using the command LOAD DATA INFILE, and got the above errors.

Last night I downloaded mysqlfrontend from zdnet, and used its import data facility and it now works fine. Strange, as mysqlfrontend uses exactly the same command as I typed, and it used the same text file.

thanks for your suggestions

 

[FrdPrefct]

Are there actually quotes around each field in the db? I'm thinking there's a space, or a special character that's not showing up. Of course, this would make your query not return any rows. If there ARE quotes in the table, you will probably have to search on the quotes too.

...frd

 

[lojze]

As I can see you use double " sign after where. Use single ' sign . If mySql works as Ms- SQL these must work.
Lojze