Several hours of confusion sorted but not 100% sure why.
SELECT 'phone_number,day'
FROM client_phone
INNER JOIN message_instance
ON client_phone.clientid=message_instance.clientid
WHERE day LIKE '%t%'
Produces the expected result with several number, day pairs (I meant to put the single quotes around one column not two on the first line but it still works).
But if I put single quotes around the column day in the WHERE clause, I get zero hits.
SELECT 'phone_number,day'
FROM client_phone
INNER JOIN message_instance
ON client_phone.clientid=message_instance.clientid
WHERE 'day' LIKE '%t%'
Just grateful if someone could explain what is going on. IE why “day” works and " ‘day’ " does not.
Single quotes are for strings. You’re probably confusing them for (backtick, below the ~ tilde), which help distinguish columns in a database and allows you to use alternative characters like spaces. ie: [B]SELECTcolumn namefromtable name` WHERE 1;[/B]
WHERE ‘day’ LIKE ‘%t%’ doesn’t work because it’s false. There is no t in day. You’re not comparing the column day, you’re comparing the string day to %t%. If you had WHERE ‘day’ LIKE ‘%y%’ it would be the same thing as saying WHERE 1, which is always true.
If SELECT ‘phone_number,day’ works, then I have no idea why. Everything in my head tells me it’s wrong and won’t work.
it returns a number of rows like this – Code:
‘phone_number,day’
‘phone_number,day’
‘phone_number,day’
‘phone_number,day’
‘phone_number,day’
‘phone_number,day’
…
Because you’re selecting a string as the value to return instead of the value of the column.
I’m a little confused to what you’re saying, but I think you’re still a little off.
The WHERE works fine. You’re comparing the string value “day”, not the column day. It’s the same as WHERE 1=1.
I think you’re confused thinking that the first part HAS to be a column, it doesn’t. You can even reverse it like: SELECT * FROM table WHERE 1=column_name will return all the rows where column_name = 1.
And as you can see from your original query, it doesn’t even have to have a column name in it at all. You’ll actually see alot of databases or ORMs transform SELECT statements without a WHERE clause to SELECT * FROM table WHERE 1=1; which means to select everything. (I’m actually not sure why they do this… can you answer that @r937 for my own benefit? )
just for programmer convenience since you can just add additional conditions with AND… after that and it has no impact on execution time.
Probably should clarify that, before I confuse anyone…generally, you would never build your queries like that, because you know exactly what you are querying the DB for. An ORM on the other hand has no idea ahead of time what you are querying or how, the list of conditions is not known at compile time and is instead built at run time…that’s where the WHERE 1=1 comes in handy
You sir, are 100% correct. I just saw I was getting a line of output and no error message.
My excuse is working over 16 hours in a Red Cross shelter evacuation after only two hours sleep waiting for hurricane that never arrived with only three clients. And we are STILL open in case we get hit this afternoon… Boring
Anyway thanks once again - I stand corrected And with the help here I am getting rapidly less bad!
1st hurricane hit the Big Island and lost most of its umph ande the second one veered away. So pretty much another drill - but practice makes perfect for when we really get hit!
)
And with the help here I am getting rapidly less bad!