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

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

这是我的查询

  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 
   
 
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongyi1939 2013-08-12 17:01
    已采纳

    So your trying to get the last 6 messages of a person if I'm right?

     SELECT 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 IN (Contact.mobileNo, Contact.workNo, Contact.homeNo, Contact.other)
     WHERE Message.User_id = 23
    GROUP BY Message.mobileNo
     ORDER BY Message.idTextMessage DESC LIMIT 6
    
    评论
    解决 无用
    打赏 举报
  • 查看更多回答(2条)

相关推荐 更多相似问题