try to get active and non active from two different date rows

[Tanja]

I try to sort which kennels are active (have a litter last 8 years AND / OR a bitch with max age 8 years) and other hand which are not active.
It could be that a kennel never had a litter but an active bitch.

+---------------+---------------+---------------+---------------+---------------+
|kennelname     |first litter   |last litter    |oldest bitch   |youngest bitch |
+---------------+---------------+---------------+---------------+---------------+
|green          |2006-01-15     |2020-08-02     |2002-01-06     |2020-08-02     |
|red            |2007-11-19     |2007-11-19     |2005-01-29     |2011-05-14     |
|blue           |1997-11-26     |2011-12-18     |1994-10-22     |2006-12-19     |
|yellow         |2014-03-22     |2019-06-29     |2010-12-02     |2013-10-07     |
|silver         |               |               |2016-11-08     |2016-11-08     |
|black          |               |               |2002-02-23     |2009-05-28     |
+---------------+---------------+---------------+---------------+---------------+

green ist active, there is a litter and a bitch under 8
red and blu are inactive, no litter and no bitch under 8
yellow is active - litter under 8 but no bitch
silver is active - no litter (yet) but bitch under 8
black is inactive - no litter and no bitch

I tried with different JOIN versions, but never get right results. My last attempt (UNION) also does not deliver the desired values, i get totaly wrong dates for some kennels.
For result first ones must be females, for second ones not (because in a litter could born only males)

One table is owner (id and kennelname), the other is dog, with breeder_id and owner_id (breeder and owner could be same ones)
Any ideas?

 

SELECT 
owner_bitch.id, owner_bitch.kennelname, owner_bitch.country, owner_bitch.country_short, owner_bitch.kennel_note, 

bitch.id AS dogid, bitch.date_of_birth, bitch.gender, bitch.owner_id,  
max(bitch.date_of_birth) as youngest,
max(bitch.date_of_birth) as youngestbitch
FROM owner AS owner_bitch
LEFT JOIN dog AS bitch ON owner_bitch.id = bitch.owner_id
WHERE  bitch.gender='female' 

AND owner_bitch.kennelname !=''
AND owner_bitch.kennel_note =''
AND owner_bitch.country_short ='de'

UNION

SELECT
breeder_litter.id, breeder_litter.kennelname, breeder_litter.country, breeder_litter.country_short, breeder_litter.kennel_note, 
litter.id AS litter_id, litter.date_of_birth AS litter_date_of_birth, litter.gender, litter.breeder_id,  
max(litter.date_of_birth) as youngest,
max(litter.date_of_birth) as youngestlitter
FROM owner AS breeder_litter

LEFT JOIN dog AS litter ON breeder_litter.id = litter.breeder_id
WHERE breeder_litter.kennelname !=''
AND breeder_litter.kennel_note =''
AND breeder_litter.country_short ='de'


GROUP BY kennelname
HAVING youngest <= DATE_SUB(CURDATE(), INTERVAL 8 YEAR) 

 

Edited April 2, 2022 by Tanja
forgotten something

[Barand]

Is the table of data at the top of your post supposed to be any way related to the query at the bottom of the post? I don't see a connection.

[Barand]

Given your initial table and your desire to find active kennels, then

table: kennel

+-----------+------------+--------------+-------------+--------------+----------------+
| kennel_id | kennelname | first_litter | last_litter | oldest_bitch | youngest_bitch |
+-----------+------------+--------------+-------------+--------------+----------------+
| 1         | green      | 2006-01-15   | 2020-08-02  | 2002-01-06   | 2020-08-02     |
| 2         | red        | 2007-11-19   | 2007-11-19  | 2005-01-29   | 2011-05-14     |
| 3         | blue       | 1997-11-26   | 2011-12-18  | 1994-10-22   | 2006-12-19     |
| 4         | yellow     | 2014-03-22   | 2019-06-29  | 2010-12-02   | 2013-10-07     |
| 5         | silver     |              |             | 2016-11-08   | 2016-11-08     |
| 6         | black      |              |             | 2002-02-23   | 2009-05-28     |
+-----------+------------+--------------+-------------+--------------+----------------+

query

SELECT kennelname
	 , CASE WHEN timestampdiff(YEAR, last_litter, CURDATE()) < 8
              OR timestampdiff(YEAR, youngest_bitch, CURDATE()) < 8
            THEN 'Active'
            ELSE ''
            END as 'active'
FROM kennel

results

+------------+--------+
| kennelname | active |
+------------+--------+
| green      | Active |
| red        |        |
| blue       |        |
| yellow     | Active |
| silver     | Active |
| black      |        |
+------------+--------+

But I am not sure that is solving your real problem.

[Tanja]

kennelname comes from table owner, first / last litter is MIN / MAX date in dog (and here then via breeder_id), oldest / youngest bitch also from dog via owner_id.

F.E. i am breeder green (id 1) and breed last litter on 2020-08-02 (and hold a bitch from this litter in my kennel), so breeder_id and owner_id in dog is also 1

 

Oldest and youngest bitch and first/last litter must be build from dog data (date of birth).

I try to get first only the active and in another query (other site) the non active ones...

[Barand]

What, precisely, are the table structures here. Your second query of the UNION above seems to be getting litter dates from the owner table(???).

Does your dog table hold every dog from every litter? How do know when an owner still holds a bitch from a particular litter?

You mention breeder_id and owner_id - are these diffrerent entities or are the terms interchangeable?

[foxclone]

I agree with Barand. Without seeing all the table structures involved, it's impossible to make recommendations. Another question comes to mind. Are there any foreign keys established between the tables involved?

[Tanja]

Table dog contains (among other things) id, birthday, sex, owner_id and breeder_id.
Table owner contains (among other things) id, kennelname, kennel_note, country, country_short. Kennel_note is for "special" people which don´t breed correct way. If filled, no display 😉. Maybe the name of table owner is inconvenient - it is breeder and/or owner. An owner could be also a breeder, then there is a kennelname. If there is no kennelname - it is not a breeder.

owner_id and breeder_id are different fields (i could be the breeder and you owner), so they are not interchangeable.
Every dog has a breeder, but not every dog has an owner.

2 hours ago, Barand said:

Does your dog table hold every dog from every litter?

It is daily work to complete, but most of them are complete. Maybe some breeder buy a bitch at age 3 - query must intercept that.

2 hours ago, Barand said:

How do know when an owner still holds a bitch from a particular litter?

Users are able to edit the owner of dogs - so if i hold one i enter me as owner.

table dog:
+-------+---------------+---------------+-------+---------------+---------------+
|id     | dogname       | birthday      | sex   | owner_id      | breeder_id    |
+-------+---------------+---------------+-------+---------------+---------------+
| 1     | dog1 green    |2020-08-02     |male   |               | 1             |
| 2     | dog2 green    |2020-08-02     |female | 1             | 1             |
| 3     | dog3 green    |2000-01-01     |female | 1             | 3             |
| 4     | dog4 red      |2007-11-19     |male   |               | 2             |    
| 5     | dog5 green    |2020-08-02     |female | 5             | 1             |
| 6     | dog6 yellow   |2019-06-29     |male   | 4             | 4             |
| 7     | dog7 yellow   |2019-06-29     |female | 7             | 4             |
+-------+---------------+---------------+-------+---------------+---------------+

table owner:

+-------+---------------+---------------+
| id    | name          | kennelname    |
+-------+---------------+---------------+
| 1     | green horn    | green         |
| 2     | red lady      | red           |
| 3     | blue boy      | blue          |
| 4     | yellow girl   | yellow        |
| 5     | silver woman  | silver        |
| 6     | black man     | black         |
| 7     | white angel   |               |    
+-------+---------------+---------------+

[Barand]

In 2020 kennel A has litter from its only bitch.

In 2021 the bitch that had the litter is sold to the breeder at kennel B

Which is the now active kennel?

Do you track who the breeder/owner was at the time of the litter?

[Tanja]

6 hours ago, Barand said:

In 2020 kennel A has litter from its only bitch.

In 2021 the bitch that had the litter is sold to the breeder at kennel B

Both - kennel A had a litter and kennel B the bitch

query-part for the last litter in connection with the breeder_id and the query-part for the bitch via the owner_id

7 hours ago, Barand said:

Do you track who the breeder/owner was at the time of the litter?

I use data which are in database at the time the page is called. It is not important who was owner before.
The breeder is always the same for a dog, if a dog is sold only the owner is changed.

[Barand]

9 hours ago, Tanja said:

Both - kennel A had a litter and kennel B the bitch

How do you know A had the litter if you only have the data at the time the page is called?

[Tanja]

as soon as I register a dog, it also has a breeder. Therefore without static values.

Currently I'm trying a solution with php and if (after mysql).

Maybe it's not really possible to distinguish between two dates - and/or no dates - in a reasonable way

[Barand]

Try

mysql> SELECT kennelname as `Active Kennel`
    -> FROM owner
    ->      JOIN
    ->      dog ON dog.owner_id = owner.owner_id
    -> WHERE timestampdiff(YEAR, birthday, CURDATE()) < 8
    ->             AND sex = 'female'
    ->             AND kennelname <> ''
    -> UNION
    -> SELECT kennelname
    -> FROM owner
    ->      JOIN
    ->      dog ON dog.breeder_id = owner.owner_id
    -> WHERE timestampdiff(YEAR, birthday, CURDATE()) < 8
    ->             AND kennelname <> ''
    -> ;
+---------------+
| Active Kennel |
+---------------+
| green         |
| silver        |
| yellow        |
+---------------+