Challenges students face when learning to work with relational databases and SQL

Re:Challenges students face when learning to work with relat... by magicalhippo on Sunday August 29, 2021 @08:25PM (#28351758)

Heh, perhaps it's more accurate to say I've abused recursive queries.

One instance was to turn a column containing comma separated values into rows[1], so I could join on them. Wasn't for a query that needed performance of course.

[1]: https://news.ycombinator.com/item?id=28020321

[ Parent | on HN ]

Re:Challenges students face when learning to work with relat... by alexanderskates on Monday August 30, 2021 @05:01AM (#28353874)

I had this same issue in Redshift and ended up populating a table with values 1 to the maximum number of commas found (e.g. using max(regexp_count(...)) or something), then cross joining on the table with the csv column and calling split_part on the corresponding column and index (with the index coming from the numbers table). The cross join ensures that you index every value of the csv column.

[ Parent | on HN ]

Re:Challenges students face when learning to work with relat... by fifilura on Monday August 30, 2021 @05:53AM (#28354165)

What I think you'd do here (in modern implementations of SQL at least) is to split the values into an array using some convenient split function and self join on that array to pivot the arrays into rows.

[ Parent | on HN ]

Re:Challenges students face when learning to work with relat... by magicalhippo on Monday August 30, 2021 @08:42AM (#28355041)

> some convenient split function

If only the DB we're using had one of those :)

[ Parent | on HN ]