sqlmariadb

Query rows which meet multiple conditions


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


Solution

  • 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
    

    DEMO