How to join two tables where a keyword is within the results of one of them?

Midnight 0
Viewed 32

I'm struggling a little with wording the question, so I'm sorry if that was rather vague. I'll try to describe it better here.

I have the following two tables:

Table A (strategies):

+-------+-----------+
|  id   |   name    |
+-------+-----------+
| 10001 | strategy1 |
| 10002 | strategy2 |
| 10003 | strategy3 |
+-------+-----------+

Table B (attributes):

+----+-------------+---------+------------+------------+
| id | strategy_id | type_id |   value    |    date    |
+----+-------------+---------+------------+------------+
|  1 |       10001 |       1 | Helsinki   | 2022-07-01 |
|  2 |       10001 |       2 | Brownfield | 2022-07-01 |
|  3 |       10002 |       1 | Paris      | 2022-08-01 |
|  4 |       10002 |       2 | Greenfield | 2022-08-01 |
|  5 |       10003 |       1 | Helsinki   | 2022-09-01 |
|  6 |       10003 |       2 | Greenfield | 2022-09-01 |
|  7 |       10001 |       1 | Frankfurt  | 2022-09-22 |
+----+-------------+---------+------------+------------+

Only the value with the latest date for each respective type is valid at a given time. I.e. the attribute of type 1 for strategy 10001 that's valid is the one with attribute_id 7 and value Frankfurt because 2022-09-22 > 2022-07-01 for the two competing rows with the same strategy and type id.

What I'm trying to accomplish is having an SQL statement that looks for a certain keywords within the attribute values and strategy name and return only those strategy ids where matches were found.

I figured out how to get only the latest attributes for a given strategy:

SELECT *
FROM attributes
WHERE strategy_id = 10001
AND (strategy_id, type_id, date) IN (
    SELECT strategy_id, type_id, max(date)
    FROM attributes
    GROUP BY strategy_id, type_id
)

I figured I use GROUP_CONCAT(value) (in a MySQL db) instead of * in the select part, I can condense the results to a single line (e.g. "Brownfield,Frankfurt" for strategy 10001) that could be used for a LIKE comparison to search all the attribute rows in one operation. I'm unsure how to combine that result with the strategy table to get all the strategies where matches were found (either in the strategy name or one of its attributes).

join sql
2 Answers

you can try this

with table_1 (ID,strategy_id,type_id,value,date)
as
(
  Select    '1','10001','1','Helsinki   ','2022-07-01'
  union all Select '2','10001','2','Brownfield ','2022-07-01'
  union all Select '3','10002','1','Paris      ','2022-08-01'
  union all Select '4','10002','2','Greenfield ','2022-08-01'
  union all Select '5','10003','1','Helsinki   ','2022-09-01'
  union all Select '6','10003','2','Greenfield ','2022-09-01'
  union all Select '7','10001','1','Frankfurt  ','2022-09-22'
),
table_2 (ID,name)
as
(
            Select '10001','strategy1'
   union all Select '10002','strategy2'
   union all Select '10003','strategy3'
)
Select 
    *
FROM
    (
    Select 
        x.ID,
        x.name,
        max(y.date) as Date
    from table_2 as x
    left join table_1  as y ON
    x.ID = y.strategy_id
    group by 
        x.ID,
        x.name
    ) a
inner join table_1  as y ON
    a.ID = y.strategy_id and
    a.[Date] = y.date
user199506270

I figured it out myself.

This is the SQL query I came up with that gives me the result I'm looking for:

SELECT DISTINCT *
FROM strategies
WHERE id IN (
    SELECT strategy_id
    FROM attributes
    WHERE (strategy_id, type_id, date) IN (
        SELECT strategy_id, type_id, max(date)
        FROM attributes
        GROUP BY strategy_id, type_id
    )
    AND LOWER(value) LIKE LOWER("%Helsinki%")
    GROUP BY strategy_id
    ORDER BY strategy_id
)
OR LOWER(name) LIKE LOWER("%Helsinki%")

This will return strategy3 but not strategy1 (which has changed type1 from Helsinki to Frankfurt on 2022-09-22). It will also return strategy1 if I'm using its name as search criteria.

Midnight0
Related