2

(I really struggle with how to ask this question correctly with few words, so sorry for the title. I tried my best.)

I have a database like this:

id animal date no
1 dog blank 0
2 cat blank 0
3 dog 07/12 2021 3
4 dog 08/12 2021 2
5 cat 08/12 2021 4

(The table is a simplified table from a larger project in Android Studio with java.)

I want to query this datebase so that:

  • I get the animals on a certain day
  • If an animal is not given for å certain day, I want the row with that animal and a blank date

Examples:

  • If date is 08/12 2021, I want rows with id 4 and 5.
  • If date is 07/12 2021, I want rows with id 3 and 2 (no cat that day)
  • If date is 06/12 2021 (or any other date), I want rows with id 1 and 2 (no cat nor dog that day)

I know I can get all from a certain date pluss those with blank dates, by:

@Query("SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) 
        ORDER BY no ASC")

But, how can I get what I want instead?

Karina
  • 31
  • 5
  • Is the id 1 and 2 rows some kind of "dummy" rows? Strange design. – jarlh Dec 08 '21 at 17:41
  • Yes, kind off. They are supposed to be the "fall-back" options. (I have left out some columns to make the question easier to grasp, but those columns have the information I need.) – Karina Dec 08 '21 at 17:51
  • Add 'Top 1' to your query and you've solved it. – JeffUK Dec 08 '21 at 17:52
  • Are the animales fixed? Only dog or cat? Or there is -n- animals? – Leandro Bardelli Dec 08 '21 at 17:54
  • There can be a "cow" without any "blank date". But if it is possible to solve my problem only without the "cow", that's certainly better than nothing! – Karina Dec 08 '21 at 17:57
  • Ok, do you have a calendar table? Left join on that and you have your answer https://stackoverflow.com/questions/14105018/generating-a-series-of-dates – JeffUK Dec 08 '21 at 18:12
  • Does this answer your question? [MySQL: Select All Dates In a Range Even If No Records Present](https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present) – JeffUK Dec 08 '21 at 18:14
  • I don't think a calender solves my problem (or maybe my thoughts are so stuck that I don't see how that is the solution). I have no problem getting the rows from my date (or even date range), but adding to it if data is missing, is the part I don't get. – Karina Dec 08 '21 at 18:26

2 Answers2

1

According to your sample data, I think that your datamodel has none right setup.

You will get a problem with this data:

id animal date no
6 cat 09/12 2021 2
7 cat blank 0
8 dog 12/12 2021 3

→ Therefore you must calculate the time-difference between last valid date and previous valid date and have to make differences, when your last date is less then your request date.

e.g. request at 11/11 2021, where your last valid date would be 09/12 2021

It would be better if you set to every INSERT a system-timestamp. Something like this:

id animal date no change_date
6 cat 09/12 2021 2 09/12 2021
7 cat blank 0 10/12 2021
8 dog 12/12 2021 3 12/12 2021

With this setup you have the advantage that you do not have to implicitly assign the ID to possible dates.

With this the result can easily be queried:

SELECT
  *
FROM
  db
WHERE
  db.change_date = '11/12 2021'

UPDATE

If you only want to show a result is available for a certain date:

@Query("SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) AND db.animal = 'cat'
        ORDER BY nr ASC limit 1
        UNION ALL
        SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) AND db.animal = 'dog'
        ORDER BY nr ASC limit 1")
Andreas Hauser
  • 92
  • 8
  • 1
    I think I understand your point and really appreciate your time! But I see that my question is not clear enough. (Sorry, I think it's very difficult to be clear!) If I request 11/11 2021 and I have neither a cat nor a dog there, I just want the empty ones. I do not want the previous valid date, but either what I have on that date or the empty ones. Did that make sense? – Karina Dec 08 '21 at 19:19
  • In the updated section you will get the result for 'cat' and 'dog' with the UNION function. – Andreas Hauser Dec 08 '21 at 19:53
  • Nice! Is it possible to loop through the animals in the query? Or do I have to change the query every time a new animal type is added? (There will be _many_ animals.) – Karina Dec 08 '21 at 20:01
1

After hours of googling, I actually found the answer. It is really so simple that I feel like I should have gotten it earlier:

@Query("SELECT *, MAX(db.id) FROM db WHERE (db.date LIKE :date OR db.date LIKE :blank) GROUP BY db.animal")

This code simply gives the row with the largest id for each animal since the data is grouped by animal. It couldn't be easier.

Karina
  • 31
  • 5