Tanja Posted April 2, 2022 Share Posted April 2, 2022 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/ Share on other sites More sharing options...
Barand Posted April 2, 2022 Share Posted April 2, 2022 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. Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594824 Share on other sites More sharing options...
Barand Posted April 2, 2022 Share Posted April 2, 2022 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. Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594825 Share on other sites More sharing options...
Tanja Posted April 2, 2022 Author Share Posted April 2, 2022 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... Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594826 Share on other sites More sharing options...
Barand Posted April 2, 2022 Share Posted April 2, 2022 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? 1 Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594828 Share on other sites More sharing options...
foxclone Posted April 2, 2022 Share Posted April 2, 2022 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? Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594829 Share on other sites More sharing options...
Tanja Posted April 2, 2022 Author Share Posted April 2, 2022 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 | | +-------+---------------+---------------+ Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594832 Share on other sites More sharing options...
Barand Posted April 2, 2022 Share Posted April 2, 2022 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? Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594835 Share on other sites More sharing options...
Tanja Posted April 3, 2022 Author Share Posted April 3, 2022 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. Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594848 Share on other sites More sharing options...
Barand Posted April 3, 2022 Share Posted April 3, 2022 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? Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594849 Share on other sites More sharing options...
Tanja Posted April 3, 2022 Author Share Posted April 3, 2022 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 Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594850 Share on other sites More sharing options...
Barand Posted April 3, 2022 Share Posted April 3, 2022 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 | +---------------+ Quote Link to comment https://forums.phpfreaks.com/topic/314655-try-to-get-active-and-non-active-from-two-different-date-rows/#findComment-1594856 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.