In a table called "db1.metadatas" I have two columns I want to use. The first column is called "meta_struct_id". The second column is called "value". I want to get the values of the meta_struct_id "1" and "40" but pick up only both values when the value of meta_struct_id "1" is like "YEAR-2000%"
I have tried different queries but can't get the exact result I need.
I have tried
SELECT value FROM db1.metadatas m WHERE meta_struct_id IN (1, 40)
but cannot workout how to pick up only both values when the value of meta_struct_id 1 is like "YEAR-2000%"
The column are as follows:
record_id: | meta_struct_id: | value:
4 | 1 | YEAR-2000-DEBIT-O1
4 | 40 | $3,000.20
4 | 6 | Kansas City
8 | 1 | YEAR-2000-DEBIT-02
8 | 40 | $15,604.50
8 | 6 | Los Angeles
20 | 1 | YEAR-2000-DEBIT-03
20 | 40 | $12,550.01
20 | 6 | Auckland
51 | 1 | YEAR-2020-DEBIT-01
51 | 40 | $6,050
I am only showing the 1, 40 and 6 but the table has much more meta_struct_ids listed but they are grouped by record_ids. The data if fictitious.
The result would look like this:
(This is id 1) (This is based on id 40)
YEAR-2000-DEBIT-01 $3,000.20
YEAR-2000-DEBIT-02 $15,604.50
YEAR-2000-DEBIT-03 $12,550.01
Any help would be appreciated
Use a self-join:
SELECT m1.value AS `(This is id 1)`, m2.value AS `(This is based on id 40)`
FROM db1.metadatas AS m1
JOIN db1.metadatas AS m2 ON m1.record_id = m2.record_id
WHERE m1.meta_struct_id = 1
AND m1.value LIKE 'YEAR-2000%'
AND m2.meta_struct_id = 40