You probably want to add type to both the address and phone tables. Then you
can be selective in your reporting and still get 1 row per student in your
result set. Just remember if your data has the possibility of not having the
information for a student you want to use LEFT JOIN's vs INNER JOIN's or the
student with no "primary" phone [in the following statement] will not be
included in the result set.
SELECT student_id,
name,
age,
h.street_name AS home_address,
s.street name AS school_address,
n.num AS primary_phone
FROM student s
LEFT JOIN address s
USING (student_id)
LEFT JOIN address h
USING (student_id)
INNER JOIN phone_num n
USING (student_id)
WHERE h.type = 'Home'
AND s.type = 'School'
AND n.type = 'Primary'
-----Original Message-----
From: Koon Yue Lam [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 24, 2005 12:34 PM
To: mysql@lists.mysql.com
Subject: DB design question
Hi, here is the case:
one student may have more than one address, and one student may have more
than one phone number
so the db would be:
student
----------------
student_id
name
age
address
---------------
address_id
student_id
street_name
phone_num
------------------
student_id
num
extension
the key of 3 tables are student_id
the problems is, when I want to query both student, address and phone num,
the sql will be
select * from student s, address a, phone_num n
where s.student_id = a.sudent_id
and s.student_id = n.student_id
it won't provide a nice result as data of student are repeated in every row,
address and phone_num's data are repeated in certain rows
The output is not suitable for reporting and may I ask what is the better
way of design to handle the above case ?
any help would be apreciated
Regards
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
