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

If each date can only have one of each type (i.e. there can't be 2 'A' or 2 'B' for the same date), then you can group by the date and use conditional aggregation to get the values for the different types.

select
    date,
    max(case when type = 'A' then id end) idA,
    max(case when type = 'A' then value end) valueA,
    max(case when type = 'B' then id end) idB,
    max(case when type = 'B' then value end) valueB
from V1
where type in ('A', 'B') -- only necessary if other types exist
group by date
having count(distinct type) = 2

http://sqlfiddle.com/#!9/69b9f/1

Answer 2

This is maybe not the most obvious query to write.
But it can make sense as your are pivotting on a date and merging the types with a date.
The performance would be very depended on the used index(es) which are unknown.

SELECT 
 *
FROM (

  SELECT 
     date
   , MAX(CASE WHEN type = 'A' THEN type END) AS  typeA
   , MAX(CASE WHEN type = 'A' THEN value END) AS  valueA
  FROM 
   your_table
  WHERE
   type = 'A' 
  GROUP BY 
   date  
) AS a_type 
INNER JOIN (

  SELECT 
     date
   , MAX(CASE WHEN type = 'B' THEN type END) AS  typeB
   , MAX(CASE WHEN type = 'B' THEN value END) AS  valueB  
  FROM 
   your_table
  WHERE
   type = 'B' 
  GROUP BY 
   date   
) AS b_type
ON
 a_type.date = b_type.date

Result

| date       | typeA | valueA | date       | typeB | valueB |
| ---------- | ----- | ------ | ---------- | ----- | ------ |
| 2019-06-01 | A     | 1      | 2019-06-01 | B     | 2      |
| 2019-06-09 | A     | 11     | 2019-06-09 | B     | 14     |

see demo

Answer 3

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 4

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'