CODEWITHSUNDEEP
mysqlsql
Rocco The Taco
Rocco The Taco

Reputation: 3797

MySQL DISTINCT ignored in SELECT displays duplicate

I have the following query that is joining 3 tables but is displaying duplicate results where I only one instance of a record by using DISTINCT on the r.Email column. 

SELECT DISTINCT FirstName, LastName, r.Email, ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID=r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID=d.firmID
    WHERE ListingFirmID != 'BrokerC'

The data looks like this, the ListingID information being unique is why I believe the query is showing the Agent ID. How do I overcome this?

Buck      |     Rogers     |     [email protected]     |     656565      |      BrokerAA
Buck      |     Rogers     |     [email protected]     |     787878      |      BrokerAA
Pamm      |     Feeets     |     [email protected]     |     999999      |      BrokerBB

The result I'd like is this:

Buck      |     Rogers     |     [email protected]     |     656565      |      BrokerAA
Pamm      |     Feeets     |     [email protected]     |     999999      |      BrokerBB

Upvotes: 0

Views: 79

Answers (2)

Ambrish
Ambrish

Reputation: 3677

Better try Group by and min() function

SELECT FirstName, LastName, r.Email, min(ListingID) as ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID = r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID = d.firmID
    WHERE ListingFirmID != 'BrokerC'
GROUP BY FirstName, LastName, r.Email, ListingFirmID

With above query you will get distinct combination of FirstName, LastName, r.Email, ListingFirmID along with minimum value of ListingID.

Upvotes: 4

Sathish
Sathish

Reputation: 4487

try like this. 

SELECT FirstName, LastName, r.Email, ListingID, ListingFirmID
    FROM sancap_Residential_Property e 
    JOIN sancap_Agent r ON e.ListingAgentID=r.AgentID
    JOIN sancap_Office d ON e.ListingFirmID=d.firmID
    WHERE ListingFirmID != 'BrokerC'
group by FirstName, LastName, r.Email,ListingFirmID

Upvotes: 2

Related Questions