I am fairly new to SQL and this is a really complex query for me. Here is the setup. 3 tables. tbl_personnel, tbl_personnel_dras, tbl_dra
each person in the tbl_personnel table can have 0 - 3 records in the tbl_personnel_dras table. The tbl_personnel_dras table is just the person_id (from tbl_personnel) and an area_id that corresponds to the field 'id' in tbl_dra.
I am trying to display the person record with all of the areas (by name) that are associated with them. here is what I have so far.
SELECT DISTINCT p.fname, p.lname, w.web_link, a1.dra_id, a2.dra_id, a3.dra_id, dra1.area as areaa, dra2.area as areab, dra3.area as areac
FROM tbl_personnel p, tbl_personnel_weblinks w, tbl_personnel_dras a1, tbl_personnel_dras a2, tbl_personnel_dras a3, tbl_dra dra1, tbl_dra dra2, tbl_dra dra3
WHERE p.id = w.person_id
AND p.id = a1.person_id
AND p.id = a2.person_id
AND p.id = a3.person_id
AND a1.dra_id = dra1.id
AND a2.dra_id = dra2.id
AND a3.dra_id = dra3.id
This query works, but if the person only has one record in tbl_personnel_dras I get the same value in areaa, areab and areac
Thanks for any help. Charles
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
