FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MYSQL Normalization double join





Magicman
I am working on creating a MYSQL database of college students and I have normalized the majors (that is I made a new table full of majors and reference them in the student table via ID numbers). My problem is that some students have two majors and I can't figure out how to get the database to return both. I have two columns in the student table (major1 and major2). I want a call that will output the names of both majors. I've tried joins but the closest I've gotten is to have the student returned twice with a different major each time. Does anyone know how I can achieve this?
badai
t_student:
student, major_1, major_2

t_major:
seq, major_label

t_student.major_1 and t_student.major_2 should store t_major.seq

this is the query:
Code:
select
t1.student
, t2.major_label as major_1
, t3.major_label as major_2
from t_student t1
left join t_major t2 on t2.seq = t1.major_1
left join t_major t3 on t3.seq = t1.major_2


this might result in null for major_2 for single major. if you don't like it, do this:
Code:

select
t1.student
, t2.major_label as major_1
, if (isnull(t3.major_label), 'NONE', t3.major_label) as major_2
from t_student t1
left join t_major t2 on t2.seq = t1.major_1
left join t_major t3 on t3.seq = t1.major_2

replace NONE with any text you want to display major_2 for single major student.
Magicman
Thanks. That does exactly what I wanted it to do. It seams so obvious now that two left joins would do what I wanted.

What is the technique where you use t1,t2, etc. as a shortcut to the full table called?
badai
are you doing your homework or what? if you need to write it in you paper, you better find out yourself from proper source. i might give you the wrong term like "i don't want to repeatedly copy-paste that damn long table name" technique. you might get an F then.

in case you didn't notice, without creating that shortcut name, you can never have the second join.

oh, while you are looking for the term of that shortcut name, why don't you look for the term when you want to obtain information from two separate tables like this. it has its own term. two words, start with P and J. it will really impress your teacher. he/she might not know it. that is why you need to look in proper source. you can't just tell them you get it in wiki. oh, in wiki they didn't mention that term, in case you want to look there.
Fire Boar
Magicman wrote:
Thanks. That does exactly what I wanted it to do. It seams so obvious now that two left joins would do what I wanted.

What is the technique where you use t1,t2, etc. as a shortcut to the full table called?


It's called aliasing. "table1 AS a", or "table1 a" for short, tells SQL that "a" should be used as an alias for "table1".
Magicman
Thanks, Fire Boar. I thought that aliasing was something different because in all my research I didn't see the shorthand of just saying "table1 a". Badai, I wasn't doing homework, this is for a personal project. I just wanted to learn more about the technique and I couldn't very well Google "i don't want to repeatedly copy-paste that damn long table name technique."
phphunger
Hi Magicman, i hope this will give you some advantage. please check this link .. http://www.phphunger.com/2012/06/learning-joins-in-mysql-database-with.html#.UAZTu2G0N3w
Related topics
MYSql
Help with mysql query
phpBB Error, seems to be a MYSQL DB error...
Master Tutorial on SEO
[Community Project] Easy Simple Content Management System
mysql syntax error I can't find why
need help with mysql joins
Project to join - php,mysql??
Question about a php and mysql script.
[Edit] mySQL Count
Wanted: UrbanStreets Project (flash game) members
Improvements needs to be done!!!
MySQL and PHP Question
Difference of single quote and double quote in mysql
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.