How to read multiple strings and dates in mySQL table in one read statement?

Question

I have a mySQL table similar to following example :

DOC_OwnerID   DOC_type    DOC_start_date    DOC_end_date   
100            JANUARY     1/1/2017          12/31/2018
100            JANUARY     1/1/2018          12/31/2019
100            DRIVER      1/5/2018          1/4/2019 
100            LICENSE     2/5/2015          2/5/2016
100            LICENSE     4/5/2018          2/5/2019
200            JANUARY     1/2/2017          1/2/2018
200            DRIVER      1/2/2018          1/2/2019

In my application logic, I need to find of any owner (DOC_OwnerID) has the three basic mandatory documents (JANUARY,DRIVER,LICENSE) per given time period. Count of 3 is necessary to show owner has three documents. (Document names are unique per time period)

For an example : OwnerID = 100, Date = 4/9/2018

true =>     100            JANUARY     1/1/2018          12/31/2019
true =>     100            DRIVER      1/5/2018          1/4/2019 
true =>     100            LICENSE     4/5/2018          2/5/2019

Should return 3 to show all three documents are valid for the given date. I could use COUNT but how am I select the records as DPC_Type is not unique.

But owner = 200 never will be true since he does not have LICENSE recorded.

I could do this in my application by reading all records for the owner. How could I do this in sql at once ?

Thanks, Pubudu

What do you want as output? – Sameer Reza Khan Apr 06 '19 at 20:26 — Sameer Reza Khan, Apr 06 '19 at 20:26

GMB · Accepted Answer · 2019-04-06T20:31:01.530

1

You could use aggregation:

SELECT DOC_OwnerID
FROM mytable
WHERE  @mydate >= DOC_start_date AND @mydate <= DOC_end_date
GROUP BY DOC_OwnerID
HAVING
    MAX(DOC_type = 'JANUARY') = 1
    AND MAX(DOC_type = 'DRIVER') = 1
    AND MAX(DOC_type = 'LICENSE') = 1

This will return DOC_OwnerIDs that, for the given @mydate parameter, have all three DOC_type values.

edited Apr 06 '19 at 20:31

answered Apr 06 '19 at 20:25

GMB

216,147
25
84
135

1

Thank you. Having is the key. – PCG Apr 06 '19 at 20:48
Is it possible to read the doc_type in run time from another table and get the same thing ? Doc_types will change based on owner. – PCG Apr 07 '19 at 02:23

score 1 · Answer 2 · answered Apr 06 '19 at 20:41

You want to return all valid rows from the table, right?
So you must join the results after getting the doc_owner_ids that satisfy the conditions to the table:

select t.* 
from tablename t inner join (
  select doc_owner_id 
  from tablename
  where 
    str_to_date('4/9/2018', '%m/%d/%Y') between doc_start_date and doc_end_date
    and
    doc_type in ('JANUARY', 'DRIVER', 'LICENCE')
  group by doc_owner_id
  having count(distinct doc_type) = 3
) g on g.doc_owner_id = t.doc_owner_id
where
  str_to_date('4/9/2018', '%m/%d/%Y') between t.doc_start_date and t.doc_end_date
  and
  t.doc_type in ('JANUARY', 'DRIVER', 'LICENCE')

How to read multiple strings and dates in mySQL table in one read statement?

2 Answers2