Left Join Query Problem

[crhodus]

I have 3 tables: PROJECTINFO, RECEIVEREC, CONTACTS. PROJECTINFO is my parent table and RECEIVEREC & CONTACTS are the 2 child tables. These two child tables have a one to many relationship with the PROJECTINFO table.

Instead of permanetly deleting records from these 3 table, I am recording the user name of the person who clicked on the delete button. This way, if someone accidentally deletes a record, I can go back in and retrieve it for them.

I want to pull all the PROJECTINFO records that have Adams in the County field, along with the RECEIVEREC and CONTACTS records that belong with the project. I don't want to see record that have been "deleted", so I am checking to make sure that the WHODELETE field in each table is null.

I have a project named "My Project 1" that is not being pulled by the query. "MY Project 1" has a few child records in the RECEIVEREC and CONTACTS tabled that have been "deleted" - they have JDoe recorded in the WHODELETE field. The problem is that this record "MY Project 1" is not being displayed when I run the query.

Can someone tell me what I'm doing wrong? BTW I've been testing & running this query in SQL Server Query Analyzer.

Thanks,
Crhodus

Here is my query:

Select
PROJECT, COUNTY,LOCATION, LANDCOST, INFRACOST, ARCHCOST, OTHERCI,
FEDSUP, STATESUP, COUNTYSUP, CITYSUP, PRIVATEINV, TTLPUBPRIV,
TOTALPUB, SUBTOTALP, FNAME, LNAME, EMAIL, ADD1, ADD2, CITY, STATE, ZIP
From
(PROJECTINFO LEFT JOIN CONTACTS ON PROJECTINFO.PROJNUM = CONTACTS.PROJNUM)
LEFT JOIN RECEIVEREC ON PROJECTINFO.PROJNUM = RECEIVEREC.PROJNUM
WHERE (PROJECTINFO.WHODELETE IS NULL)
AND (RECEIVEREC.WHODELETE IS NULL)
AND (CONTACTS.WHODELETE IS NULL)
AND (County = 'Adams' )
ORDER BY PROJECT, TYPERECORD

 

[SQL_Hell]

Hi,

Just to clarify, you have run the SQL in QA and it works ok, but when you run the query in your project it does not return any data,

is this correct?

 

[crhodus]

Sorry if I wasn't clear. I'm running it throught SQL QA and it is not running correctly.

 

[SQL_Hell]

Still not clear on what you saying,

does the query show any error message?
or does it not return any data?

If the query was wrong then it would show an error?

What is 'My project 1'? is this an Access project (front end)

 

[crhodus]

The query is not returning any data.

 

[SQL_Hell]

maybe then there is no data that matches your criteria, and your LEFT joins.


Remove some of the criteria and re-run the query, if this query was wrong then you would get an error.

 

[crhodus]

I've got a record in the PROJECTINFO that has a county named 'Adams'. Let's say that this record's project number is 123. At one time project 123 had several records in the CONTACTS table and the several records in the RECEIVEDREC table. These child records have now been "deleted". The way they are deleted is that the user's name is recorded in the WHODELETE field for the records in CONTACTS and the RECEIVEDREC table.

With this query, I want to pull all the records that that have a county named 'Adams'. If the project has any child records tht do not have a user name recorded in the WHODELETE field (the project's child records are not deleted), then I want to pull this data also.

In project 123's case, the query should return the following fields with data from the PROJECTINFO tableROJECT, COUNTY,LOCATION
The following field from the RECEIVEDREC table should be null:LANDCOST, INFRACOST, ARCHCOST, OTHERCI,
FEDSUP, STATESUP, COUNTYSUP, CITYSUP, PRIVATEINV, TTLPUBPRIV,
TOTALPUB, SUBTOTALP
Also, the following field from the CONTACTS table should be null:
TOTALPUB, SUBTOTALP, FNAME, LNAME, EMAIL, ADD1, ADD2, CITY, STATE, ZIP


Please let me know if I need to clarify.

Thanks!

 

[crhodus]

Does anyone have any suggestons? Here is a sample copy of the query I'm trying to run.