CODEWITHSUNDEEP
mysqlsqldatabaseselectdistinct
Matas
Matas

Reputation: 186

SQL Distinct clause not working?

I'm new to SQL so sorry if this is me being stupid!

I am trying to select a few columns where the name does not appear more than once, however using the distinct clause does not do this for some reason?

Here is my query:

SELECT DISTINCT A.fldContactName, A.fldsignonlinesetup, B.fldorderdate, A.fldemail
FROM tblcustomers AS A
LEFT JOIN tblorders AS B ON A.fldcustomerid = B.fldcustomerid
WHERE B.fldorderdate BETWEEN '2013-01-01' AND '2016-12-31'
AND A.fldemail <> 'NULL' AND A.fldcontactname <> 'NULL' AND A.fldcontactname <> '' 
AND A.fldemail <> ''
AND A.fldsignonlinesetup = 0
ORDER BY A.fldcontactname ASC

How do I get it to only show records that are unique e.g. contact name John Smith only comes up once?

Expected Outcome:

fldContactName:    fldEmail:        fldSignOnlineSetup:    fldOrderDate:
James Smith        [email protected]         0              2016-08-14
Bill Plant         [email protected]         0              2015-02-24

Actual Outcome:

fldContactName:    fldEmail:        fldSignOnlineSetup:    fldOrderDate:
James Smith        [email protected]         0              2016-08-14
Bill Plant         [email protected]         0              2015-02-24
James Smith        [email protected]         0              2014-06-20

Upvotes: 2

Views: 742

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want the naming only appearing once, then group by comes to mind. One method is:

SELECT c.fldContactName,
       MAX(c.fldsignonlinesetup) as fldsignonlinesetup,
       MAX(c.fldorderdate) as fldorderdate,
       MAX(c.fldemail) as fldemail
FROM tblcustomers c LEFT JOIN
     tblorders o
     ON c.fldcustomerid = o.fldcustomerid
WHERE o.fldorderdate BETWEEN '2013-01-01' AND '2016-12-31' AND
      c.fldemail <> 'NULL' AND c.fldcontactname <> 'NULL' AND 
      c.fldcontactname <> '' AND c.fldemail <> '' AND
      c.fldsignonlinesetup = 0
GROUP BY c.fldcontactname
HAVING COUNT(*) = 1
ORDER BY c.fldcontactname ASC;

SELECT DISTINCT just makes sure that all the columns in the result set are never duplicates. It has nothing to do with finding values with only one row. The HAVING clause does this.

Notes:

  • The use of table aliases is good, but abbreviations for table names make the query more understandable.
  • The MAX() is really a no-op. With one row, it returns the value from the one row.
  • The GROUP BY is on the field you care about -- the one you don't want duplicates for.
  • The HAVING clause gets the values with only one row.
  • MySQL does not require the MAX() functions, but I strongly recommend using an aggregation function, so you don't learn bad habits that don't work in other databases and can behave unexpected in MySQL.
  • Do you really mean fldemail <> 'NULL' or do you intend A.fldemail IS NOT NULL?

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Do you want to display your selected data only once or get only the data that is unique in the DB?

For the latter do

SELECT A.fldContactName, A.fldsignonlinesetup, B.fldorderdate, A.fldemail
FROM tblcustomers AS A
LEFT JOIN tblorders AS B ON A.fldcustomerid = B.fldcustomerid
WHERE B.fldorderdate BETWEEN '2013-01-01' AND '2016-12-31'
AND A.fldemail not in ('NULL', '') 
AND A.fldcontactname not in ('NULL', '')
AND A.fldsignonlinesetup = 0
GROUP BY A.fldContactName, A.fldsignonlinesetup, B.fldorderdate, A.fldemail
HAVING count(*) = 1
ORDER BY A.fldcontactname ASC

Upvotes: 0

Related Questions