How to query from multiple tables where one table may not have data

[denjermen]

If anybody can help me with this I will be forever grateful. I'm no programmer nor SQL expert, but weekly I have to run a report compiling data from several tables into an excel spreadsheet. I figured I could just write a simple SQL to do that for me. Man, was I wrong. Here is what I have:

SELECT re_prop.glc, pm_master.projid, re_prop.addr1, re_prop.city, re_prop.state, re_prop.puse, re_disposition.source_list, pm_master.projtype, re_disposition.status, re_lease.curr_term_dt, pm_master.projdescription, re_disposition.disp_year, re_disposition.disp_month, re_prop.gsf, re_disposition.dispose_sqft, re_prop.acres, re_disposition.disp_acres, re_disp_financial.gross_sale, re_disp_financial.npt_gain_land, re_disp_financial.npt_loss_land, re_disposition.in_market_dt, re_disposition.under_contract_dt, re_disposition.proj_comp_dt, re_disposition.reo_dir, re_disposition.trans_spec, re_disposition.comments, pm_master.projid, pm_master.projcomment, re_disposition.proj_owner
FROM re_disposition
left join pm_master on re_disposition.data_id=pm_master.data_id
left join re_prop on re_disposition.data_id=re_prop.data_id
left join re_lease on re_disposition.data_id=re_lease.keyid
inner join re_disp_financial on re_disposition.recid=re_disp_financial.dispid
WHERE re_prop.state in ('AL','FL','GA','KY','MS','TN','IL','IN','MI','NC' ,'SC','OH','WI','WV') and re_prop.status in ('A', 'S') and re_disposition.source_list = 'PROJECT X' and re_disposition.status in ('COMPLETED','PENDING','APPROVED','IN DEVELOPMENT') and pm_master.projtype in ('Sale','Partial Sale','Lease Termination') and pm_master.statuscode in ('P','D', 'H')

As you can see, I have to combine info from different tables for this report. My problem comes at the end, where I'm trying to limit the data to the type of project (pm_master.projtype) and to the project's status (pm_master.statuscode). In pm_master, there are several "projects" under the same data id, so by limiting it to the projects I need, I will not get info from projects that do not pertain.

However, my problem occurs when there has not been a project set-up in pm_master. Normally a LEFT JOIN will show the proj because it's in the re_disposition table, but when I add the criteria for pm_master and no projects has been set-up in pm_master, the query result does not show the project in re_disposition at all.

I've tried using NULL and '' to no avail.... I'm going crazy over this and it's become an obsession of sorts.

Can anybody help me with this? I've been looking around the net for almost two weeks now and have not been able to find a solution. Once again, your help is greatly appreciated!

[code green]

However, my problem occurs when there has not been a project set-up in pm_master. Normally a LEFT JOIN will show the proj because it's in the re_disposition table, but when I add the criteria for pm_master and no projects has been set-up in pm_master, the query result does not show the project in re_disposition at all

If I follow this correctly, why can you simply not remove the criteria?

[denjermen]

If I follow this correctly, why can you simply not remove the criteria?

First off, thank you for looking into this issue for me. Yes, you are correct, if I remove the criteria, the info will show, but my dilema is that there are multiple projects under the pm_master table that does not pertain. The pm_master table keeps tracks of the status of projects for the properties. There are numerous projects for each property, some of which have been completed, others are for projects that do not pertain to the subject matter. I need to pull up projects that are the types listed: 'Sale','Partial Sale','Lease Termination' and are in status 'P','D',etc..

But, like I said, there are some projects in re_disposition that does not have a corresponding project set-up in pm_master and hence my dilema. If I do not put in the critera, I get results from other projects that do not pertain or have been completed. If I put in the criteria, I do not get the projects in re_disposition because there is no corresponding project in pm_master.

I will like to be able to pull all of the projects in re_disposition and if there is a corresponding project under the given criteria, for it to limit the projects to the criteria, but if there is not a corresponding project in pm_master, then to just list the project in re_disposition and leave the info that would've been in pm_master blank.

Once again, thanks for looking at this!

[code green]

Sounds a complicated situation.
I don't really have time to study your dilemma as I am going on holiday today.
But it looks like an extra field may help. Can you modify the database?
If so insert a field called say `projects_pertaining`,
then you could apply an integer code to each record denoting the common projects or under which manager it belongs or anything you like.
A sub-table called say `project_identifier` could list all the codes along with a description.
A bit of a cop-out I know but it would be a powerful way of grouping projects of certain types, managers or relevance.
Sorry if I am way off the mark here but your explanation is rather involved

[denjermen]

Thanks for looking at the issue anyway... Anybody else with a suggestion???