CODEWITHSUNDEEP
mysqlsqlselectconcatenation
Adry
Adry

Reputation: 307

Mysql using SELECT CONCAT results as a condition in the same query

I'm trying to use this query where I need to concatenate a string before compare to obtain the right results. The query is running, but is not finding any results. I believe the problem is when I try to compare "myname" in the WHERE clause. Any idea on what's the right query for this?

SELECT * 
FROM (SELECT CONCAT('tm_',name) AS myname FROM room) room, users
WHERE user_name = myname;

Upvotes: 0

Views: 1036

Answers (2)

DocJones
DocJones

Reputation: 677

Why not

SELECT * FROM users,room where user_name=concat('tm_',name);

?

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Try this instead

SELECT *
FROM 
(
   SELECT CONCAT('tm_',name) AS myname 
   FROM room
) t
WHERE user_name = myname

May be you need to JOIN the other table users like so:

SELECT *
FROM 
(
   SELECT CONCAT('tm_',name) AS myname 
   FROM room
) t
INNER JOIN users u ON ... --some join condition
WHERE user_name = myname

Or:

SELECT *
FROM 
(
   SELECT CONCAT('tm_',name) AS myname 
   FROM room
) t, users
WHERE t.user_name = myname"

Upvotes: 0

Related Questions