SQL statement inside a table

What I want to do is have a SQL join inside a table. Here is what it looks like:

pie table

Id       type message
1        2    'hello'
2        0    'bye'
3        2    'meh'

cake table

Id      data     pieId   
1       70       1@2
2       6        2
3       8        1@3@2

That is what the two tables look like, and what I want to do is be able to select the cake table where data = 70 and have it return the row 1 of cake table, and return rows 1 and 2 of pie table.

I don't really care that it returns them as a separate table, row, join, or column.

What I really want is to be able to ask the database for data = 8 and be able to return 1, 2,and 3 from pie table. I don't really care how it works if you have any suggestions

So I want to be able to query the cake and get a dynamic amount of pies. Even the table can change if you can think of some way of doing this.

Solution

Looks like you need to update your database design

I would get rid of the pieId from cake table and create a relationship table. Lets call it CakePies with Id, PieId, CakeId. Just add the relationship to that table

The query would be

    Select * 
from Cake c
Inner join CakePies cp on c.Id = cp.CakeId
inner join Pies p on p.Id = cp.PieId
where Data = 70 -- or whatever filter you want

Update Your CakePies table should look like the following:

CakeID   PieId
1        1
1        2
2        2
3        1
3        2
3        3