I am fairly new to SQL and this is a really complex query for me.
Possibly more complex than necessary. Must you have a separate column for each of the areas? If you don't mind having them all in a single column, the query becomes simple and efficient:
SELECT p.fname, p.lname, w.web_link, a.dra_id, a.area FROM tbl_personnel p LEFT JOIN tbl_personnel_weblinks w ON p.id=w.person_id LEFT JOIN tbl_personnel_dras a ON p.id = a.person_id LEFT JOIN tbl_dra dra ON a.dra_id = dra.id;
If you must have the separate columns for each area, let me know. I'm pretty sure it can still be done, but it will be *ugly*. Basically, you'll have to join three more copies of the tbl_dra and tbl_personnel_dras tables, something like what you have below, but with left joins instead... and then put in a condition saying that dra1.id > dra2.id > dra3.id > dra4.id, but modify it to allow NULLs for dra4.id or dra3.id and dra4.id or dra2.id, dra3.id, and dra4.id, or all four of them. Like I said, *ugly*.
Bruce Feist
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
--------------------------------------------------------------------- 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
