php - Junction tables in MySQL -
Hi, I am learning how to work with junction tables in MySQL. I
(Sorry, my english is very good And I do not have the impression of doing nothing. I know the basics of MySQL but I have never worked with "JOIN"
In this test project, I have a given category app I would like to show on page (you click on "games", only "games" Apps appearing on the line will be the same on the page) I would like to know how the SQL request should look.
The second question, we say that an app can fit into 2 different sections, how can I give my APP2 different category_id in my database?
At this time my database looks like this:
Table name: APPS
+ ---------- - + ------------------- + | App_ID (PK) | App_Name | + ------------ + ------------------- + | 1 | Weather Network | | 2 | Is this sunny 2.0? | 3 | Weather app | | 4 | Zelda | | 5 | Megaman | 6 | Doom 3 + ------------ + ------------------- +
Table name: CATEGORY
+ ----------------- + ----------------- + | Category_ID (PK) | Category_name | + ----------------- + ----------------- + | 1 | Sports | | 2 | Weather | + ----------------- + ----------------- +
of the table Name: JUNCTION_APP_CATEGORY
+ ---------------- + ------------------- - + | APP_ID (PK) | Category_ID (PK) | + ---------------- + -------------------- + | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 1 | | 5 | 1 | | 6 | 1 | + ---------------- + -------------------- +
For the first question, the answer is
SELECT a. *, C. * From APPS A, Category C, JUNCTION_APP_CATEGORY AC WHERE a.App_ID = AC.APP_ID and c.Category_ID = AC.Category_ID and AC. Category _ID = & lt; Category_id category "games" & gt; For your second question, you can use APP_ID
and Categor_ID
as both primary keys of the table JUNCTION_APP_CATEGORY
(ignore two pks , But use two columns together as a PK). So that you can insert data like this: + ---------------- + ------------- ------- + | APP_ID (PK) | Category_ID (PK) | + ---------------- + -------------------- + | 1 | 1 | & Lt; - APP_ID = 1 Both cat 1 & amp; 2 | 1 | 2 | | 2 | 2 | | 3 | 2 | | 4 | 1 | | 5 | 1 | | 6 | 1 | + ---------------- + -------------------- +
Comments
Post a Comment