dpm91915
2013-08-12 16:56
浏览 22
已采纳

在sql查询中不起作用

i have two tables in my db .. one is Messages and the other is Contacts... both tables contain the mobile_number field.. in Contacts table there is no duplicate numbers ... but in Messages tables there are several duplicate numbers in mobileNo field...what i am doing is write now i am selecting the distinct mobile numbers from the Messages tables and then i am comparring the distinct numbers from the contacts table ... so if the messages_mobileNo is found on the contacts table then give me the contact name against the number otherwise messages_mobileNo ... so the problem is distinct not working .. i am not able to get the distinct numbers from the Messages table ... it is showing me the duplicate numbers

here is my query

 SELECT DISTINCT Message.mobileNo,
            Contact.mobileNo,
            Contact.workNo,
            Contact.homeNo,
            Contact.other,
            Contact.name,

            Message.body,
            Message.idTextMessage
FROM cakephp_db.textmessage AS Message
LEFT JOIN cakephp_db.contacts AS Contact ON (Message.user_id = Contact.user_id
                                         AND ((Message.mobileNo = Contact.mobileNo)
                                              OR (Message.mobileNo = Contact.workNo)
                                              OR (Message.mobileNo = Contact.homeNo)
                                              OR (Message.mobileNo = Contact.other)))
 WHERE Message.User_id = 23
 ORDER BY Message.idTextMessage DESC LIMIT 6

图片转代码服务由CSDN问答提供 功能建议

我的数据库中有两个表..一个是消息</ strong>,另一个是< strong>联系人</ strong> ...两个表都包含mobile_number字段..在Contacts表中没有重复的数字...但在Messages表中,mobileNo字段中有几个重复的数字...我正在做的是写 现在我从消息</ strong>表中选择不同的手机号码然后我正在比较联系人表格中的不同号码...所以如果在联系人表格中找到messages_mobileNo,那么给我联系人姓名 反对数字否则messages_mobileNo ...所以问题明显不起作用..我无法从Messages表中获取不同的数字...它显示重复的数字</ p>

这是我的查询</ p>

  SELECT DISTINCT Message.mobileNo,
 Contact.mobileNo,
 Contact.workNo,
 Contact.homeNo,
 Con  tact.other,
 Contact.name,
 
 Message.body,
 Message.idTextMessage 
FROM cakephp_db.textmessage AS Message 
LEFT JOIN cakephp_db.contacts AS Contact ON(Message.user_id = Contact.user_id 
 AND  ((Message.mobileNo = Contact.mobileNo)
 OR(Message.mobileNo = Contact.workNo)
 OR(Message.mobileNo = Contact.homeNo)
 OR(Message.mobileNo = Contact.other)))
  WHERE Message.User_id = 23 
 ORDER BY Message.idTextMessage DESC LIMIT 6 
 </ code> </ pre> 
 </ div>

3条回答 默认 最新

相关推荐 更多相似问题