Advertisements
I have two tables: Test1 and Test2.
Test1:
| Cat | Dog | Date |
|---|---|---|
| 123412 | 1111111 | 2023-01-01 12:01:01 |
| 123412 | 2222222 | 2023-01-01 13:02:01 |
Test2
| Cat | Parrot | Date |
|---|---|---|
| 123412 | 1412111 | 2023-01-01 14:01:01 |
| 123412 | 2242222 | 2023-01-01 15:02:01 |
The script for joining these tables looks like this:
select
test1.cat
test2.cat
from Test1 test1
left join Test2 test2 on test2.cat=test1.cat
It is necessary to make sure that only 1 cat is displayed for the maximum date (without duplicates). Using a subquery or conditions inside a JOIN or WHERE.
I tried to do this subquery:
select
test1.cat
test2.cat
from Test1 test1
left join Test2 test2 on test2.cat=test1.cat
and test2.date = (select max(test2.date) from Test2)
where test1.date = (select max(test1.date) from Test1)
but nothing happened, it displays emptiness
I apologize if my explanations are unclear
>Solution :
Have you tried something like this?
SELECT Cat,MAX(date) as maxDate
FROM (
SELECT test1.cat, test1.date
FROM Test1 test1
UNION
SELECT test2.cat, test2.date
FROM Test1 test2
)
GROUP BY Cat.