Jeff Moden (10/8/2013)
Bhuvnesh (10/8/2013)
ChrisM@Work (10/8/2013)
SELECT * FROM TABLE WHERE countryid = @country
SELECT * FROM TABLE WHERE countryid = @country AND stateID = @stateid
SELECT * FROM TABLE WHERE countryid = @country AND stateID = @stateid AND Cityid = @Cityid
totally agree with you Chris.. but i think here OR operator will be more reliable because with correct data
OR and AND will return same records ..What do you think ?
Gosh, I wouldn't rely on that nuance especially for the sake of clarity. I would absolutely use AND in this case just to avoid any confusion in the heat of an emergency fix, should it ever occur. A newbie wouldn't understand the ORs are an implied AND simply because of the IDs involved.
Can you please explain how the OR is an implied AND. I don't see OR & AND returning the same records.
CREATE TABLE MyTable
(UserID INT,
CityID INT,
StateID INT,
CountryID INT);
-- Two users, in the same state and country but different cities
INSERT INTO MyTable VALUES (1, 1, 1, 1);
INSERT INTO MyTable VALUES (2, 2, 1, 1);
SELECT
*
FROM
MyTable
WHERE
CityID = 1
OR StateID = 1
OR CountryID = 1;
SELECT
*
FROM
MyTable
WHERE
CityID = 1
AND StateID = 1
AND CountryID = 1;
DROP TABLE MyTable;