SELECT * FROM WHERE does not find all records

Question

When I type:

select * from 'saleslog' where 'Status' = 'Pending';

or

select * from 'saleslog' where 'Status' = "Pending";

or

select * from saleslog where Status = 'Pending';

despite the fact that there are hundreds of rows with "Pending" value in Status column I get only 3 records displayed. The same happens when I look for value other than "Pending".

If however, I type:

select * from saleslog where status like "%Pending%";

then most if not all records are displayed. There are absolutely no spaces or any other characters in front and behind Pending value.

I am wondering if table "saleslog" needs to be repaired and if so, how? I'm kind of new to SQL.

score 4 · Accepted Answer · edited Dec 14 '14 at 23:41

4

It's possible there are hidden characters in the field that you just can't see such as tab, carriage return or line feed. Have you tried doing an update on the field to try and correct it? Maybe try running the update query below and then run your SELECT query again:

UPDATE saleslog SET status = 'Pending' WHERE status LIKE '%Pending%'

edited Dec 14 '14 at 23:41

potashin

44,205
11
83
107

answered May 01 '14 at 01:29

cmreynol

121
3

UPDATE saleslog SET status = 'Pending' WHERE status LIKE '%Pending%' Query OK, 240 rows affected (0.21 sec) Rows matched: 243 Changed: 240 Warnings: 0 THIS DID THE TRICK. Thank you! Seems like all rows are now being displayed. – user3387040 May 01 '14 at 04:00

potashin · Answer 2 · 2014-05-01T01:27:22.780

1

Try the following:

UPDATE `saleslog` SET `status` = TRIM(`status`);
SELECT * FROM `saleslog` WHERE `status` = 'Pending';

edited May 01 '14 at 01:27

answered May 01 '14 at 01:08

potashin

44,205
11
83
107

I get "ERROR 1064 (42000): You have an error in your SQL Syntax:..." – user3387040 May 01 '14 at 01:14
1

I also use third party PHP software like Adminder Editor and searching for records in it brings to same results. – user3387040 May 01 '14 at 01:15
Oh, I have just copied and pasted your code and it did displayed three records with "Pending" value in Status column. No error this time - perhaps I did typo first time. Still remaining 100s of records were not found/displayed. – user3387040 May 01 '14 at 01:23
I have tried as you have suggested and here is what I've got, but then I did what cmreynol suggested and seems it worked. mysql> UPDATE `saleslog` SET `status` = TRIM(`status`); Query OK, 0 rows affected (0.01 sec) Rows matched: 2007 Changed: 0 Warnings: 0 SELECT * FROM `saleslog` WHERE `status` = 'Pending'; Table here... 3 rows in set (0.00 sec) – user3387040 May 01 '14 at 04:03

SELECT * FROM WHERE does not find all records

2 Answers2