Usually it's best to work with normalized tables, which would make this trivial. tbl_reports isn't normalized, since it has a simulated array of persons in it. Could it be split into two tables:
tbl_reports, with fields: rep_id (primary key) and other report-specific information you didn't mention below
tbl_pers_rpt, with fields rep_id, person, (together they are the key) and other information you didn't mention below?
If you must stay with the denormalized design, you can still do the query. I'll show you what it would look like for just two persons, and you can generalize.
SELECT *
FROM tbl_reports r LEFT JOIN tbl_personnel p1 ON r.person1 = p1.per_id
LEFT JOIN tbl_personnel p2 ON
r.person2 = p2.per_id
;If you need more reasons that denormalized tables are usually not a good idea, just ask.
Bruce Feist
Charles Kline wrote:
tbl_reports has these fields:
rep_id, person1, person2, person3, person 4
tbl_personnel has these fields:
per_id, fname, lname, mname
What is the way to get each report back once, and have the fname, mname, and lname fields available to print to the screen in PHP for each person1, person2, person3, person4 (which contain per_id)?
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
