Looking for the most efficient select query

Question

I have a table and need the result over different lines where different key values in one column must exist.

I'm getting this table once a day via REST API from another system and looks in my MySQL DB like this (simplfied):

+----+------+------------+-------+
| ID | type | date       | value |
+----+------+------------+-------+
|  1 | A    | 2019-06-01 |     1 |
|  2 | B    | 2019-06-01 |     2 |
|  3 | A    | 2019-06-02 |     4 |
|  4 | B    | 2019-06-03 |     5 |
|  9 | A    | 2019-06-09 |    11 |
| 10 | B    | 2019-06-09 |    14 |
| 11 | A    | 2019-06-24 |    99 |
+----+------+------------+-------+

Now, I need a select which results all only where a value exists for type A and type B at the same date. The result should look like this:

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+

• First idea:

Using subselects... probably bad idea for large tables... but I'm not sure.

SELECT * from 
(SELECT date as dateA, value as valueA from V1 where type = 'A') AS subA,
(SELECT date as dateB, value as valueB from V1 where type = 'B') AS subB
WHERE dateA = dateB

• 2nd idea:

Creating two temporary tables and then to a join. But needs time to create two tables and the the join over both new temporary tables.

CREATE TEMPORARY table tA SELECT date, type as typeA, value as valueA from V1 WHERE type = 'A';
CREATE TEMPORARY table tB SELECT date, type as typeB, value as valueB from V1 WHERE type = 'B';

SELECT * from tA 
INNER JOIN tB on tA.date = tB.date;

• 3rd idea:

Creating only on temporary table and use this for a join using the main table:

CREATE TEMPORARY table tB SELECT * from V1 WHERE type = 'B';

SELECT * from V1 
INNER JOIN tB on V1.date = tB.date
where V1.type = 'A'

Which of my ideas is the best for a large table or is there a better solution.

Thank in advance.

Answer 1

You need a self inner join

select select v1.date,v1.type as typeA, v1.value as valueA, 
       v2.id, v2.type as typeB, v2.date, v2.value
  from v v1 join v v2    -- v is table name
    on v1.date = v2.date
    and v1.type = 'A' and v2.type = 'B';

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+

Demo

Answer 2

an efficent way could be based on a inner join between the date with distinct count = 2 and the table itesel one time for ype A and one tyme for type b

    select t.date, t1.type typeA, t1.value valueA, t2.type typeB, t2.value valueB, 
    from (
        select  date
        from  my_table  
        group by  date  
        having count(distinct type) = 2
    ) t 
    inner join  my_table t1 on t1.date = t.date and type='A'
    inner join  my_table t2 on t2.date = t.date and type='B'