Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Filter Countries, States and Cities which in same row in one table. Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 2:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 826, Visits: 2,664
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;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1502968
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse