CODEWITHSUNDEEP
mysql
leoarce
leoarce

Reputation: 549

If same name is in table multiple times and a certain column has 2 different kinds of data don't select any of those rows with that name

I have this query

SELECT DISTINCT visitors_table.name, visitors_table.visitor_submitted 
FROM visitors_table 
LEFT JOIN list_data 
ON visitors_table.vid = list_data.id 
WHERE visitors_table.cid='2050' AND list_data.seed != '1'

And result is this

aaron 0000-00-00 00:00:00
brandon 2016-05-24 09:48:27
will 0000-00-00 00:00:00
amy 0000-00-00 00:00:00
amy 2016-05-24 17:14:27
amy 2016-05-24 17:20:00

I want to select all the ones where date equals 0000-00-00 00:00:00, but if that person has another record where the date is not all zeros then i don't want to select that person.

So i can do this

SELECT DISTINCT visitors_table.name, visitors_table.visitor_submitted 
FROM visitors_table 
LEFT JOIN list_data 
ON visitors_table.vid = list_data.id 
WHERE visitors_table.cid='2050' AND list_data.seed != '1' AND visitors_table.visitor_submitted='0000-00-00 00:00:00'

But that gives this result

aaron 0000-00-00 00:00:00
will 0000-00-00 00:00:00
amy 0000-00-00 00:00:00

That's not the result i want. i want this result, because amy has multiple entries with and without dates of zeros.

aaron 0000-00-00 00:00:00
will 0000-00-00 00:00:00

How would you go about that?

Upvotes: 0

Views: 35

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31802

You can use a correlated NOT EXISTS condition.

SELECT DISTINCT v.name, v.visitor_submitted 
FROM visitors_table v
LEFT JOIN list_data 
   ON v.vid = list_data.id 
WHERE v.cid='2050' 
  AND list_data.seed != '1'
  AND NOT EXISTS (
      SELECT *
      FROM visitors_table v2
      WHERE v2.name = v.name
        AND v2.visitor_submitted <> '0000-00-00 00:00:00'
  )

Please note that MySQL will convert your LEFT JOIN to an INNER JOIN because of this condition: list_data.seed != '1'. If you nead a LEFT JOIN, you should move that condition into the ON clause.

Upvotes: 0

imran arshad
imran arshad

Reputation: 274

First, get all the ones where date equals 0000-00-00 00:00:00 then filter out the ones that have date other than 0000-00-00 00:00:00

SELECT DISTINCT visitors_table.name, visitors_table.visitor_submitted   
FROM visitors_table 
LEFT JOIN list_data 
ON visitors_table.vid = list_data.id 
WHERE visitors_table.cid='2050' AND list_data.seed != '1' AND visitors_table.visitor_submitted = '0000-00-00 00:00:00'
and visitors_table.name not in 
(SELECT DISTINCT visitors_table.name 
FROM visitors_table 
LEFT JOIN list_data 
ON visitors_table.vid = list_data.id 
WHERE visitors_table.cid='2050' AND list_data.seed != '1' AND visitors_table.visitor_submitted <> '0000-00-00 00:00:00')

You can do the same in a different way with join, etc. if you dont have appropriate indexes and the query is too slow for you

Upvotes: 1

Related Questions