Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Nicholas Szumowski
Nicholas Szumowski
6,646 Points
Posted by Nicholas Szumowski
Nicholas Szumowski
6,646 Points

Will one of the SQL database courses help with this question...

I have a table called episodes, episodes have zero to multiple related links referenced in another table called related_links. I need to move all of the related links into the episodes table per episode row. So one episode may have 1 related link whereas another episode may have 5 related links and I need a column for each related link per episode row.

hopefully that made sense, but I'll try to diagram it:

episodes table:

id    title
--    -----------
1     ep title 1
2     ep title 2

related_links table:

ep_id     url
-----    ----------------
1         blah.com
1         google.com
1         teamtreehouse.com
2         db.com

outcome I'm looking for episodes table:

id     title          url_1        url_2           url_3                  etc...
--     -----------    ---------    ------------    ------------------
1      ep title 1     blah.com     google.com      teamtreehouse.com 
2      ep title 2     db.com

What is the term for what I'm trying to do, if there is one, and will any of the database courses here help me solve this? Thanks for any help!

andi mitre
andi mitre
Treehouse Guest Teacher
andi mitre
andi mitre
Treehouse Guest Teacher

Can easily do this with some pl/sql or regular sql for that matter.

1 Answer

anil rahman
anil rahman
7,786 Points
anil rahman
anil rahman
7,786 Points

I'm sure that it sounds soemthing like pivot/unpivot.

First if you were to just put them together you would get like 

id     title            urls        
--     -----------    ---------    
1      ep title 1     blah.com     
1      ep title 1     google.com      
1      ep title 1     teamtreehouse.com 
2     ep title 2     db.com

Then by using the pivot/unpviot you should be able to switch from row to column view to put that url column into multiple columns for each row.

id     title             url_1           url_2                 url_3                  etc...
--     -----------    ---------       ------------         ------------------
1      ep title 1     blah.com     google.com      teamtreehouse.com 
2      ep title 2     db.com
Nicholas Szumowski
Nicholas Szumowski
6,646 Points
Nicholas Szumowski
Nicholas Szumowski
6,646 Points

Thanks, pivot was the term I was looking for. Seems like this doesn't exist in MySQL, but at least I now have found a few stackoverflow posts that are working through similar issues.

In MySQL Workbench I have achieved your first step with:

SELECT
  episodes.id AS episode_id,
  episodes.title AS episode_title,
  related_links.display_text AS related_link_text,
  related_links.url AS related_link_url,
  related_links.object_id AS related_link_episode_id
FROM
  episodes INNER JOIN
  related_links
    ON related_links.object_id = episodes.id

Now to figure out how to pivot it! Thanks again