How to represent Cross Apply and Split String in MySQL
mysql, split, string, t-sql
Solution
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.
Problem
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.