CODEWITHSUNDEEP
mysqlsql
Randrian
Randrian

Reputation: 1085

MySQL query all existent and non existent entries from list (inline table)

I have a MySQL database with a table of tag names. I have a list of tags I want to assign and need to check whether they are in the database or not. Therefore I want to write a query which gives me the ids of all tags in the list which are already present and the ones which are not present yet.

In SQLite I already managed to write this query, but as it contains a CTE it can't directly be converted to MySQL.

The SQLite query is:

WITH
check_tags(name) AS ( VALUES ("name1"), ("name2) )
SELECT check_tags.name, tags.id FROM check_tags
LEFT JOIN tags ON check_tags.name = tags.name

The result would be for example:

   id   | name
   ---------------
   1    | name1
   Null | name2

In MySQL it could be something with SELECT * FROM ( VALUES("name1), ("name2") ) ... which I have seen for other database systems, but this also doesn't work with MySQL. All these different SQL dialects make searching for help difficult.

Upvotes: 1

Views: 125

Answers (1)

Randrian
Randrian

Reputation: 1085

The answer was to use an inline table as Aaron Kurtzhals pointed out. My query now is:

CREATE TEMPORARY TABLE MyInlineTable (id LONG, content VARCHAR(255) );

INSERT INTO MyInlineTable VALUES
(1, 'name1'),
(2, 'name2');

SELECT * from MyInlineTable LEFT JOIN tags on MyInlineTable.content = tags.name

Upvotes: 1

Related Questions