How to represent Cross Apply and Split String in M

问题:

For some background, what i'm trying to do create a database that contains multiple recipes. However, it's necessary for the individual ingredients to be linked to the recipe they originally came from.

For instance, I have table containing all the individual ingredients.

And a table where the recipes are stored, minus the ingredients.

Now, i've found this article which covers how to split strings using T-SQL XML Commands, and the code that is used to do so is:

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
CROSS APPLY dbo.split(Books.Authors,',') split
INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

The result i'm looking for would be very similar to this:

However, CROSS APPLY etc only works on MS SQL Server and my question is:

Is it possible to achieve the same, or very similar effect using MySQL?

Thanks for any help.

回答1:

This should quite match what you're trying to get:

SELECT
  Books.BookId,
  Books.Book,
  BookAuthors.AuthorId,
  BookAuthors.Author
FROM Books
  LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

Found this article very helpful: MySQL query finding values in a comma separated string

Leave a comment if you need further explanation how it works.