Query: How to get data from another row to fill lacking data?

user_17628164 0
Viewed 116

(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?

mysql sql
2 Answers

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")
user_135974320

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.

user_176281640
Related