How do I JOIN these tables?

Question

I have two tables:

**WEEK**

-id

-week #

**ITEM**

-id

-name

-is_marked

-week #

My final desired result is to have a table that has a rowspan="3" table cell with week # in it, followed by the three results for each week from SELECT * FROM item WHERE week = week_number AND is_marked = 1

I don't know if I need to JOIN anything because I don't really need any data from the WEEK table, but I can't quite get how I'd loop through the results to get the desired output. Thoughts?

Answer 1

Change your item table to hold the week id and not the value.

then you can do a simple join by doing:

select i.name
from item i
inner join week w on w.id = i.id
where w.week = '2' and i.is_marked = '1'

Answer 2

Ok, some thoughts.

1. Do you really need a week table in database? What purpose does it serve? It seems like a value for the item table. Its not really an entity, so may not need a separate table IMO.

2. You are right in that there is nothing to join in the two tables. If the Item table had week_id in it, instead of number, that would make sense, except for my comments in 1

3. Why only 3 items from each week? If you really want that, I would recommend you order by week number in item table. You can discard the other values in code. Use the order by clause in this case.

4. Or, you can loop through all the values in week table, for each, select from item table using week number. You can use the limit clause to return only 3 items.