M_Boy76
asked on
All records where there is a value
Hello
I am trying to retrieve all records where there is only a value in the T_Customers.Postcode with the following query, but it doesn't give me the right result, can anyone help?
SELECT T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE exists ( select * from T_Customers where T_Customers.Postcode ='');
I am trying to retrieve all records where there is only a value in the T_Customers.Postcode with the following query, but it doesn't give me the right result, can anyone help?
SELECT T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE exists ( select * from T_Customers where T_Customers.Postcode ='');
Try this
SELECT T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE T_Customers.Postcode IS NOT NULL;
SELECT T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE T_Customers.Postcode IS NOT NULL;
ASKER
Both queries returned records where the post codes are empty?
M_Boy76,
1) Please try restating what you are looking for
2) Is the Postcode column going to be NULL, or a zero-length string? They are NOT the same thing :)
Regards,
Patrick
1) Please try restating what you are looking for
2) Is the Postcode column going to be NULL, or a zero-length string? They are NOT the same thing :)
Regards,
Patrick
ASKER
I have some records that have postcodes, in the format xx11 2km, and some of these records dont have post codes,.
Now what i want to do is retrieve all records where the record has a post code.
Now what i want to do is retrieve all records where the record has a post code.
M_Boy76 said:
>>Now what i want to do is retrieve all records where the record has a post code.
Did you try tigin44's suggestion? That ought to do it...
>>Now what i want to do is retrieve all records where the record has a post code.
Did you try tigin44's suggestion? That ought to do it...
What about this:
SELECT T_Customers.CustomerID, T_Customers.CustTypeID, T_Customers.Surname, T_Customers.Forename, T_Customers.TitleID, T_Customers.Ref1,
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE len(isnull(T_Customers.Postcode,'')>0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
T_Customers.Add1, T_Customers.Add2, T_Customers.Add3, T_Customers.Add4, T_Customers.County, T_Customers.Postcode
FROM T_Customers
WHERE T_Customers.Postcode IS NULL