0

I'm trying to get the following result:

date  | country | type | amount
-------------------------------
21/09 | BE      | STK  | 20
21/09 | BE      | OPT  | 50
21/09 | BE      | CSH  | 0

based on the following tables

date  | country | type | amount
-------------------------------
21/09 | BE      | STK  | 20
21/09 | BE      | OPT  | 50


typeId | typeName                  typeName
-----------------                  --------
0      | STK              -OR-     STK
1      | CSH                       CSH
2      | OPT                       OPT

in plain text: for every day, I want the amount for every type, and to have a 0 if there is no data available for that day.

MySql does not have an outer join, and I can't get it to work with a right join. When I use a right join, I get a 'null' for everything except the type.

In reality there are 13 different types, and the other source table is not for a single day or a single country, but I suppose that that shouldn't matter.

  • MySQL has LEFT and RIGHT outer joins just fine. It's only lacking FULL joins but you don't need them for your query. Do you have a table with a list of dates for which you want these results? – ypercubeᵀᴹ Sep 22 '16 at 8:51
  • 1
    It would also be good to show us the output of SHOW CREATE tablename; (so we know the table and columns names and types, etc.) – ypercubeᵀᴹ Sep 22 '16 at 8:53
  • @ypercubeᵀᴹ: it's for daily updates of an aggregate table based on production information. The typeNames and country are varchar(select distinct from AnotherTable), date is date(datetime) and amount is int. I don't have a date table as such but I could make them with 'select distinct' – ShadowFlame Sep 22 '16 at 9:03
  • Use a left join from type to amounts, and substitute other values for any nulls that appear onthe right side of the join. – Andrew Brennan Sep 22 '16 at 9:13
  • 1
    while no solution was given, I have found the right solution thanks to the input. Thank you guys so much. @AndrewBrennan: basically what you said :D – ShadowFlame Sep 22 '16 at 9:20
1

To get all types for a specific country and a specific date, you need a simple LEFT join from types to the data table:

SELECT 
    '2016-09-21'           AS date, 
    'BE'                   AS country, 
    t.typeName             AS type, 
    COALESCE(a.amount, 0)  AS amount
FROM types AS t
  LEFT JOIN amounts AS a 
    ON  a.date = '2016-09-21'
    AND a.country = 'BE`
    AND a.type = t.typeName ;

If you want all the options but for many countries or for many dates or both, you'll need to first CROSS join the types and countries and/or dates tables, then do the LEFT join. If there isn't a countries or dates table, you can either use derived tables, either with SELECT DISTINCT or with a list of dates::

SELECT 
    d.date                 AS date, 
    c.country              AS country, 
    t.typeName             AS type, 
    COALESCE(a.amount, 0)  AS amount
FROM 
    ( SELECT DISTINCT country
      FROM data
    ) AS c                -- the countries table
  CROSS JOIN 
    ( SELECT DATE('2016-09-01') AS date UNION ALL
      SELECT '2016-09-02' UNION ALL
      ---
      SELECT '2016-09-30'
    ) AS d                -- the dates table
  CROSS JOIN 
    types AS t
  LEFT JOIN amounts AS a 
    ON  a.date = d.date
    AND a.country = c.country
    AND a.type = t.typeName ;
|improve this answer|||||
0

Use a left join from type to amounts, and substitute other values for any nulls that appear on the right side of the join using COALESCE. e.g. Assuming two tables: amounts and types:

select coalesce (a.date, sysdate), coalesce (a.country,'BE'), t.typeName as type, coalesce (a.amount,0)
from types t
left join amounts a on t.typeName=a.type;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.