Tutorial :Inner Join Question



Question:

CREATE TABLE college   (      id       SERIAL PRIMARY KEY,     SCHOOL   VARCHAR(100),    CColor   VARCHAR(100),      CCmascot VARCHAR(100)    );    CREATE TABLE mats   (      id                SERIAL PRIMARY KEY,      CColor            VARCHAR(100),      CCNAME            VARCHAR(100)    );  

MYSQL Ok so here is the problem I think its pretty simple but I am not getting it right. I have the SCHOOL name passed to me through the URL and I use the $_GET to get the college name now I need to query:
By using the SCHOOL name I need to get the CCOLOR and the CCNAME.


Solution:1

You have the college name and you wish to find out the colour name, if I understand correctly.

The linking attribute is CColor.

You query should look a little bit like this:

select      m.ccname, m.ccolor  from      mats m  inner join      college c  on      c.ccolor = m.ccolor  where      c.school = @myVariable  


Solution:2

Your question is unclear so an answer can only be approximated. You need columns in both tables that can be used to join them, that is columns that have values that can be used to identify when a record/s in the parent table (college) matches a record/s in the child table (mats). Ideally you would have a foreign key in the child table maps, which could be named college_id (this uses a naming convention that references the parent table).

Giving a foreign key like the one mentioned above your query would become

select    college.ccolor  from    college inner join mats      on college.id = mats.college_id  where    mats.ccname = "<<COLOUR_DESIRED>>";  

assuming ccname is the name of ccolor.


Solution:3

Database Tip of the Day: Use Foreign Key constraints, or you will have data corruption problems, and people on SO will have no idea how your columns relate to each other.

When you know the whys and the whatfors of relational modeling, you might find it necessary to go without them (although it's not recommended unless you have a really good reason), but for now, use them to explicitly define how the tables relate to each other.

Otherwise your question is kind of like asking a chef, "I have some unlabeled jars of food and what I think is oregano. How do I cook a romantic dinner for two?" (Umm.. what's in the jars??)

Foreign key doumentation: http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

Join documentation: http://dev.mysql.com/doc/refman/5.1/en/join.html


Solution:4

SELECT college.CColor FROM college      INNER JOIN mats ON college.CColor = mats.CColor       AND mats.CColor = 'your query'  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »