Reputation: 307
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
Reputation: 677
Why not
SELECT * FROM users,room where user_name=concat('tm_',name);
?
Upvotes: 1
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