Help with multi table query

I need help determining if files exist in multiple tables in a database

[desertwebdesigns]

I am having a problem with a multi table query. I am working on a real estate management site for a client. I have a database set up as follows:

CREATE TABLE 'appraisals' (
'pid' int(16) NOT NULL,
'filename' varchar(100) NOT NULL,
PRIMARY KEY ('pid','filename')
)

CREATE TABLE 'comps' (
'pid' int(16) NOT NULL,
'filename' varchar(100) NOT NULL,
PRIMARY KEY ('pid','filename')
)

CREATE TABLE 'photos' (
'pid' int(16) NOT NULL,
'filename' varchar(100) NOT NULL,
PRIMARY KEY ('pid','filename')
)

CREATE TABLE 'surveys' (
'pid' int(16) NOT NULL,
'filename' varchar(100) NOT NULL,
PRIMARY KEY ('pid','filename')
)

CREATE TABLE 'projects' (
'id' int(16) NOT NULL auto_increment,
'projectid' varchar(32) NOT NULL,
'name' varchar(255) NOT NULL,
'description' text NOT NULL,
'pricing' varchar(100) NOT NULL,
'amenities' text NOT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'projectid' ('projectid')
)

My client can insert a 'project' (a property basically) into the database and upload any comps, appraisals, surveys, and photos he has for that project. What I'm having trouble with is I have an incomplete projects page. What I need to do is display for him any project that does not have ALL of the associated files with it (photos, comps, surveys, and appraisals) and I've been wracking my brain for 2 days trying to figure this out. I've tried multi-table queries, joins, and sql-invoked procedures and functions and I just can't get it. I've even tried to restructure my database a couple different ways and couldn't get it to work that way either. Any help would be appreciated. I'll eventually need to use the same logic so that it will only display completed projects to clients who login. Thanks again.

[desertwebdesigns]

I got it resolved. I ended up doing a 5 table LEFT JOIN to return all the data I needed:

"SELECT p.id, p.projectid, p.name, p.description, p.pricing, p.amenities, a.filename AS afn, c.filename AS cfn, ph.filename AS phfn, s.filename AS sfn FROM projects p LEFT JOIN appraisals a ON p.id=a.pid LEFT JOIN comps c ON p.id=c.pid LEFT JOIN photos ph ON p.id=ph.pid LEFT JOIN surveys s ON p.id=s.pid GROUP BY p.id ORDER BY p.id ASC"

Once I was able to return the data I needed in the proper format I was able to use loops to find out which projects had missing files.

It works great now. Thanks to anyone who looked at this and even thought of answering, I know it wasn't an easy question. Took me 2 days to figure it out. :)