mysql outer join

Question

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.

• ypercubeᵀᴹ: 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ᵀᴹ: 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.)
• ShadowFlame: @ypercubeTM: 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'
• Andrew Brennan: Use a left join from type to amounts, and substitute other values for any nulls that appear onthe right side of the join.
• ShadowFlame: 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

Answer 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 ;

Answer 2

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;