mysql - 如何显示不同的值(value)

Question

我有一个联系人和语言表，contact 表也包含如下列

   id   Contact group   Media   E-mail                  Nationality        Country                      City      Area  Gender  Married Children    Drivers license

   107     Contactgroup1    SMS     nasir.kv01@gmail.com       India           United Arab Emirates         Dubai             Male     No       
   108     Contactgroup1    SMS     abdulrahim922@gmail.com    Pakistan        United Arab Emirates         Dubai             Male     Yes  

language 如下表

id  language
1   English
2   Hindi

我还使用帮助程序表名称 contactlanguage 将联系人 ID 与语言 ID 匹配，就像这样

contactid   languageid
   107         1
   108         1
   107         2

我在搜索功能中运行这个查询，

SELECT DISTINCT *
FROM contact
INNER JOIN contactlanguage ON contact.id = contactlanguage.contactid
INNER JOIN language ON contactlanguage.languageid = language.id
WHERE isdeleted = 0 AND language.language in ('English', 'Hindi', '')

同样的数据是这样重复的

id   Contact group     Media    E-mail                  Nationality        Country                      City      Area  Gender  Married Children    Drivers license

107     Contactgroup1   SMS     nasir.kv01@gmail.com       India           United Arab Emirates         Dubai             Male     No   

107     Contactgroup1   SMS     nasir.kv01@gmail.com       India           United Arab Emirates         Dubai             Male     No   

我只想显示一个数据谁能告诉我怎么做，谢谢

Answer 1

如果您明确仅列出联系人表中的列而不是使用 SELECT *，您将避免因联系人使用多种语言而导致的重复。

SELECT DISTINCT
        contact.id,
        contact.`Contact group`,
        contact.Media,
        contact.E-mail,
        contact.Nationality,
        contact.Country,
        contact.City,
        contact.Area,
        contact.Gender,
        contact.Married,
        contact.Children,
        contact.`Drivers license`
FROM contact
INNER JOIN contactlanguage ON contact.id = contactlanguage.contactid
INNER JOIN language ON contactlanguage.languageid = language.id
WHERE isdeleted = 0 AND language.language in ('English', 'Hindi', '');

不管怎样，您应该明确列出您想要的列，这是值得的。 It is not good practice to use SELECT *在生产代码中。