CODEWITHSUNDEEP
mysql
Phoenix
Phoenix

Reputation: 322

MYSQL concat match in where clause

I have 2 tables

Courses enter image description here

Users

enter image description here

I want to show courses where courses author = name + first name from user table

I tried

SELECT * FROM `courses` as c WHERE EXISTS ( SELECT * from `users` as u WHERE u.id_user = '63' AND c.author = CONCAT(u.name, ' ', u.surname)

I get error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Thanks.

Upvotes: 0

Views: 55

Answers (3)

Krishnakumar
Krishnakumar

Reputation: 745

Try 

select * from courses where author in (select concat(name,' ',surname)  
from Users);

Upvotes: -1

Anil
Anil

Reputation: 3752

SELECT * FROM `courses` u
join `users` u on c.author = CONCAT(u.name, ' ', u.surname)
WHERE u.id_user = '63'

U need to join here.

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

You may JOIN the two tables:

SELECT c.* FROM `courses` as c 
JOIN `users` as u 
ON c.author = CONCAT(u.name, ' ', u.surname)
WHERE u.id_user = '63'

Upvotes: 2

Related Questions