Problem with this Query, not sure what I am doing wrong

[perrydaniel]

Hi Guys,

I have the following query, which is working well, except on the part where I am looking at 'Client Status'=1. The query returns all of the ClientStatus results not just the 1's.

Any help would be gratefully appreciated.

Thanks

Perry

INSERT INTO EnquiryTemp ( PropertyID, ClientID, RequirementId, Reference )
SELECT T1.PropertyId, T2.ClientId, T2.RequirementId, T1.Reference
FROM Property AS T1, Requirement AS T2, Client AS T3
WHERE (((T1.Location)=T2.PreferredLocation) And ((T1.GuidePrice)>=T2.MinPrice And (T1.GuidePrice)<=T2.MaxPrice) And ((T1.PropertyType)=T2.PropertyType)) And ((T1.PropertyStatus)=1) And ((T3.ClientStatus)=1) And (((T1.NewProperty)=T2.NewProperty));

 

[Sashanan2]

You don't seem to have actually joined the Client table (T3) to either of the other tables, and thus the database will not know which record from T3 to associate with what, and show doubles. Is this what's going wrong?


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[perrydaniel]

Sashanan2

I am a bit of a novice on this, can you explain! How does T1 and T2 look joined, but not T3?

Thanks

Perry

 

[Sashanan2]

I can't be entirely sure without knowing the structure of your tables, but I see you joining T1 and T2 with:

((T1.Location)=T2.PreferredLocation)

and again with

(T1.PropertyType)=T2.PropertyType)

but the only reference to T3 is

((T3.ClientStatus)=1)

Without some way to tie T3 together with either T1 or T2, you don't have a join; and without a join you don't have any relation to the records retrieved from T3 and the ones from T1 and T2. Unless that's your intention, you'll need some way to join T3. I'd need to know the structure of the tables to give any more specific advice.


"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie