CODEWITHSUNDEEP
mysqlselectwhere-clause
JimmyWeb
JimmyWeb

Reputation: 103

MySQL: SELECT * FROM table WHERE field="some text" doesn't work

I am trying to INNER JOIN two tables on a text field, but I can't get it to work. I've backtracked to see if I can identify where my query is falling down. I've simplified my query to use only one table, to see if I can select data from it based on a text string. I can't make it work. I feel like an idiot. This is so basic, why isn't it doing it?

select * from `my-table`
where myKey = "some-text-data-that-i-copied-from-the-data-in-the-table";

Returns no rows. "some-text-data-that-i-copied-from-the-data-in-the-table" is in field myKey. I can see it. It's in the table!!!

[if it makes any difference I am most familiar with using an Access front-end and I am porting my "skills" to MySQL because MySQL is just better really.. this seems like the most run of the mill SELECT statement ever and yet it doesn't do anything!]

EDIT:

IMSoP does this help any...

select * from mymathssowlink
where MyMathsResourceKey = 'KS2-Number-Counting and Place Value-NC3-Negative Numbers 1';

EDIT 2:

Diego Marinelli - thanks - this returns data

select * from mymathssowlink
where MyMathsResourceKey like '%KS2%';

EDIT 3:

This works...

select * from mymathssowlink
where MyMathsResourceKey like '%KS2-Number-Counting and Place Value-NC3-Negative Numbers 1%';

EDIT 4:

But this still doesn't...

select * from mymathssowlink
where MyMathsResourceKey = 'KS2-Number-Counting and Place Value-NC3-Negative Numbers 1';

puzzled

Table:

CREATE TABLE `mymathssowlink` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ObjectiveID` varchar(13),
  `MyMathsResourceKey` varchar(100),
  PRIMARY KEY (`ID`),
  UNIQUE KEY `idnew_table_UNIQUE` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

HOW IT GOT FIXED:

okay very weird but I have fixed the simplified problem now! I remember from my Access days that trailing spaces are the bane of matches and it seems the MySQL is a bit fiddly in this regard to. There was no problem with the data as such but the field MyMathsResourceKey was the last bit of data on each line and so not terminated with a comma. Some lines didn't have any data in that column and so were terminated with a comma rather than the MyMathsResourceKey data. I fixed it by adding a third column to my csv into which i uniformly stuck the string "PLACEHOLDER". I matched this to a column called placeholder in the table receiving the data. Now that the active (2nd column) is always terminated with a comma all the data seems to work. I can't help thinking that this is a bit odd and that MySQL data imports really don't want to be behaving like this... now to try an make my JOIN work, which how this all started!

Thanks to all, especially Rahul for the help.

... and the JOIN Works now...

Upvotes: 1

Views: 13286

Answers (2)

Rahul
Rahul

Reputation: 77896

Going by all the edits in your post and comments; what I feel is, there is extra space present in your column value and so you are not getting the data (since it's not matching the exact string).

So either try like

select * from mymathssowlink
where MyMathsResourceKey 
like '%KS2-Number-Counting and Place Value-NC3-Negative Numbers 1%';

(OR)

Use a REPLACE() function to remove all spaces in the field value

select * from mymathssowlink
where REPLACE(MyMathsResourceKey, ' ', '') 
= 'KS2-Number-Counting and Place Value-NC3-Negative Numbers 1';

You can as well use TRIM() function if you can guarantee that the spaces are present only in left/right extreme of the string. 

select * from mymathssowlink
where TRIM(MyMathsResourceKey) 
= 'KS2-Number-Counting and Place Value-NC3-Negative Numbers 1';

Upvotes: 1

Diego Marinelli
Diego Marinelli

Reputation: 646

You can try Select * from table; and if that works then try select * from table where key like '%some_text%' It could not be a problem with the sintax but with the query itself.

Upvotes: 0

Related Questions