CODEWITHSUNDEEP
mysqlsql
MrBrad
MrBrad

Reputation: 29

CONCAT in WHERE clause of SELECT statement in mysql

M2014 is a text field in the DB table.

This statement works correctly (returns count = 368)

SELECT count(*) FROM arealist WHERE M2014 = 'Yes'

However, I having problems with this statement (returns count = 0) All I have changed is the concat 

SELECT count(*) FROM arealist WHERE concat('M','2014') = 'Yes'

What could be the cause and solution?

Upvotes: 2

Views: 9054

Answers (3)

echo_Me
echo_Me

Reputation: 37233

you can do it in php like that

  $year = 2014;

   SELECT count(*) FROM arealist 
   WHERE M$year = 'Yes'

Upvotes: 0

jatrim
jatrim

Reputation: 715

You can't generate a dynamic column name for the where clause in MySQL. There are a number of Stack Overflow articles to that effect. Normally, I would arrange my data so that there was some sort of date or timestamp associated with the row, rather than using date specific columns. (I'm assuming M2014 has something to do with the year 2014). When arranged this way you can select what you need based on whatever date requirements you have.

That said, if your data model is fixed, then you're best bet is probably to use another language, C#, python, whatever, to create the column names you need dynamically and then send the entire query to MySQL. Alternatively, you could write a series of SQL statements, one for date column you're interested in.

The following query in google turned up a number of relevant results: https://www.google.com/search?client=opera&q=dynamic+column+names+in+sql&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest&safe=active#channel=suggest&q=dynamic+column+names+in+mysql+where+clause&safe=active

Upvotes: 0

b.runyon
b.runyon

Reputation: 154

You are comparing two strings in the second SELECT statement. The second statement is appending two strings 'M' and '2014' which results in the query comparing 'M2014' to 'Yes' two strings, not the value of the column. Making a statement like this:

SELECT COUNT(*) FROM AreaList WHERE M2014 = CONCAT('Y','es')

That statement would return 368 rows. What are you ultimately trying to do with this statement?

Upvotes: 2

Related Questions