CODEWITHSUNDEEP
mysqlsql
Xavi
Xavi

Reputation: 2594

how show the distinct value

I am having a contact and language table,contact table with column like below also 

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

   107     Contactgroup1    SMS     [email protected]       India           United Arab Emirates         Dubai             Male     No       
   108     Contactgroup1    SMS     [email protected]    Pakistan        United Arab Emirates         Dubai             Male     Yes

for language table like below

id  language
1   English
2   Hindi

also i am using helper table name contactlanguage that matching the contact id with the language id its like this

contactid   languageid
   107         1
   108         1
   107         2

I am running this query in search function,

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', '')

the same data was repeating like this

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

107     Contactgroup1   SMS     [email protected]       India           United Arab Emirates         Dubai             Male     No   

107     Contactgroup1   SMS     [email protected]       India           United Arab Emirates         Dubai             Male     No

i want to show only one data can any one tell me how to do that,thanks

Upvotes: 1

Views: 82

Answers (1)

GarethD
GarethD

Reputation: 69749

If you explicitly list only columns from the contact table instead of using SELECT * you will avoid duplicates caused by a contact having more than one language.

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', '');

For what it's worth you should be explicitly listing the columns you want anyway. It is not good practice to use SELECT * in production code.

Upvotes: 4

Related Questions