Difficulties Understanding JOIN

[smoh]

Hey all,

Very new to SQL. I've learned how to make queries etc, but now I'm working with joining tables. A friend of mine who is a programmer has recomended that I join 2 tables together which would help. I don't quite understand why he has said that.

Here's what I'm working on...

I'm trying to create a recipes type of website. I have my 1 table with the recipes and a few other columns in there.

The 2nd table is the categories table.

The 2 common columns that I have in these are "id" and "categories". I don't think I would use id to join them since they have 2 different meanings in the tables, so I thought joining them by the field "categories" would be the way to go.

Now I don't see how this can benefit my website. I do need each recipe that is categorized in it's specific category (ex. Apple Crisp to go into the Dessert page). But what does this have to do with joining tables? Have I set up my categories table incorrectly?

ID -- categories -- sub_categories
1 --- appetizers -- NULL
2 --- BBQ -------- NULL

I'm extremely confused on how to set up this database efficiently.

Any guidance would be greatly appreciated

[tirna]

Imo for any database driven website, you should set up the database first and ensure you have appropriate tables and column names in those tables to store the date you will need to drive your website.

Part of setting up the database, especially for db's with more than 3 tables, is making an ERD (Entity relationship Diagram) which is a graphical representation of your database tables and how they relate to each other. If you haven't used an ERD before, maybe google it and read up on how they should be used.

Imo, there is no point in coding up your web pages until your db is set up. The last thing you want is half way through your web page coding realising that your db is not set up correctly.

Once you have an ERD, then coding your web pages and back end scripts becomes a lot more straight forward.

[NogDog]

My first thought is that some recipes might fall into more than one category (e.g. bruschetta might be in the "bread", "apetizer" and "Italian" categories). Therefore, you may need a separate recipe-to-category table.

recipe
==========
id (primary key)
<other columns>

category
==========
id (primary key)
category_name
<other columns>

recipe_to_category
==================
recipe_id \__ primary key on combined columns
category_id /


Sub-categories could be incorporated into the category table by adding a parent_id column which would point to the id of the applicable parent category, using a value of zero to indicate a top-level category. However, if you think you might have more than two levels (i.e. more than main categories and one level of sub-categories beneath them), you may need to look into using a "nested set" hierarchy model (see this article (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)) to facilitate traversal of the hierarchy.

PS: You might also want to take a look at this database normalization tutorial (http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html).

[criterion9]

It sounds like you are wanting a many-to-many relationship. If that is the case you will need a middle table that associates the recipes to categories.

Recipe:
ID
Name
Etc

Category:
ID
Name
etc

Recipie2Category:
recipeID
categoryID


If you are looking for a 1-to-1 relationship then you'll want to add a foreign key to your recipe table that associates it to a category.

Recipe:
ID
Name
CategoryID
etc

Category:
ID
Name
etc


EDIT: Nogdog beat me to the punch...lol.

[smoh]

I'm not quite sure I understand the whole concept in how to code these 3 tables. Like creating the parent_id as someone mentioned, I would need sub categories and I'm glad you picked up on that as well since I didn't mention it.

If I have

Recipes:
ID
Recipe
etc etc

Categories:
ID
main_cat
etc etc


recipe_to_category
==================
recipe_id
category_id

I thought you could only make 1 primary key per table? Wouldn't recipe_to_category need 2 primary keys? Or are you saying that recipe_to_category would take the id from the tables categories and recipes and put it into a cell?

Then when I want to call things into the website, do I just call from the recipe_to_category?

Sorry for all of the questions, I just want to have a firm understanding before I proceed into the coding as you experts have mentioned!

[tirna]

From the questions you are asking, I get the impression that you might be able to quickly get some benefit from working through some sql tutorials on joining tables and other types of sql queries.

If you work through these or similar tutes elsewhere you will very likely find it much easier to build your website and code all the back-end database stuff.

http://www.sql-tutorial.net/SQL-tutorial.asp

Anyway, just food for thought :)

[smoh]

I went through the JOIN tutorial on the website tirna... Thank you! I actually went through a very similar tutorial in my first php/mysql book but I don't see where the 3rd table "recipes_to_categories" comes into play. It was a good refresher on how to use JOIN statements and I have a general grasp on that.

Where would the 3rd table come in though?

[tirna]

In an earlier post I suggested you make an ERD.

If you have one, can you please post it as a jpg or pdf or some other common format because from the information in your posts I am having difficulty visualising what your actual database looks like and so I'm not sure how I can help.

I don't see why you need the third table.

[smoh]

ERD posted in http://www.webdeveloper.com/forum/showthread.php?p=1081546#post1081546

Thank you all!