mysql - My SQL 内部联接 根据另一个列的值通过两个条件连接表

Question

我有两个表 user_profile 和 chat_friendlist 返回

chat_friendslist and user_profile table

SELECT a.id, a.user_id, a.friend_id, 
IF(a.user_id=146, e.user_id, d.user_id) as conversation_user_id,   
IF(a.user_id=146, e.first_name, d.first_name) as conversation_first_name,   
IF(a.user_id=146, e.last_name, d.last_name) as conversation_last_name  
FROM chat_friendlist a   
inner join user_profile as d on a.user_id = d.user_id  
inner join user_profile as e on a.friend_id = e.user_id  
where (a.user_id = 146 or a.friend_id=146) and a.status='accepted'

The Results of above query

我得到这个结果

但现在我想添加另一个按名字和姓氏搜索的条件。

结果应按名字和姓氏过滤。

我尝试过以下查询

SELECT a.id, a.user_id, a.friend_id,  
IF(a.user_id=146, e.user_id, d.user_id) as conversation_user_id,   
IF(a.user_id=146, e.first_name, d.first_name) as conversation_first_name,   
IF(a.user_id=146, e.last_name, d.last_name) as conversation_last_name  
FROM chat_friendlist a   
inner join user_profile as d on a.user_id = d.user_id  
inner join user_profile as e on a.friend_id = e.user_id  
where (a.user_id = 146 or a.friend_id=146) and a.status='accepted'   
and d.first_name like '%g%' and e.first_name like '%g%'  

但它什么也没返回。

请帮助我

Answer 1

尝试一次

(d.first_name like '%g%' or e.first_name like '%g%')