Query to replace default values with child's values with JOIN-CodePudding

I want to JOIN on the same table, in order to get a list of Tags. My goal is that the Tags can be overwritten by the user with their own values :

Tags without accountId are the default Tags for all users.
The user can create his own Tags: no defaultTagId and a specified accountId.
The user can edit a default Tag by creating a new line, with the ID of the Tag he wants to overwrite (defaultTagId).

Tags overwritten by a user should only appear once, the default row should no longer be returned, replaced by the previously created one. In my example, "my todo" overwrite "DEFAULT TODO".

Here an example :

id	accountId	name	color	defaultTagId
1	NULL	DEFAULT TODO	#012345	NULL
2	NULL	DEFAULT DONE	#FFFFFF	NULL
3	NULL	my todo	#000000	1
4	NULL	my new tag	#ABCDEF	NULL

What I would like :

name	color
my todo	#000000
DEFAULT DONE	#FFFFFF
my new tag	#ABCDEF

Here is a start of a query that does not work as I would like :

SELECT COALESCE(Tags.name, Tags2.name), Tags.id, Tags.name, Tags.accountId, Tags2.id, Tags2.name, Tags2.accountId
FROM Tags
LEFT JOIN Tags AS Tags2 ON Tags.defaultTagId = Tags2.id AND Tags.accountId IS NOT NULL
WHERE Tags.accountId IS NOT NULL OR (Tags.accountId IS NULL AND Tags2.id IS NULL);

PS : If you can make it work with Sequelize it's even better!

CodePudding user response：

I managed to do it with the following query:

SELECT Tags.*, T2.id, T2.name
FROM Tags
LEFT JOIN Tags AS T2 ON T2.defaultTagId = Tags.id OR Tags.defaultTagId = T2.id
WHERE T2.accountId IS NULL;