Date: 04/19/07 (SQL Server)    Keywords: no keywords

I have a query that connects 4 tables.

hiree, nurse_flag_list, nurse_flag_type, facility

HIREE
id
name

FACILITY
id
facility

NURSE_FLAG_TYPE
id
flag_color

NURSE_FLAG_LIST
hiree_id (FK to HIREE)
nurse_flag_lookup_id (FK to NURSE_FLAG_TYPE)
facility_id (FK to FACILITY)

A hiree can work at multiple facilities, so a person may have multiple records in NURSE_FLAG_LIST. Would an INNER JOIN against the facility table be correct in this statement?

SELECT h.id, h.first_name, h.last_name, nf.*, f.name AS facility, nft.name AS flagcolor
FROM hiree INNER JOIN nurse_flag_list nfl ON nfl.hiree_id = h.id
INNER JOIN nurse_flag_type nft ON nft.nurse_flag_lookup_id = nfl.id
INNER JOIN facility f ON f.id = nf.facility_id

Source: http://community.livejournal.com/sqlserver/58119.html