SQL Server/T-SQL Tutorial/Query/AND
A search condition that uses the AND operator
<source lang="sql">
3> 4> 5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> select * from Bankers WHERE BankerState = "NJ" AND BankerPhone = "000-000-0000" 4> GO BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected) 1> 2> 3> drop table Bankers; 4> GO 1></source>
use the AND operator to specify that the search must satisfy both of the conditions
<source lang="sql">
6> 7> 8> create table Billings ( 9> BankerID INTEGER, 10> BillingNumber INTEGER, 11> BillingDate datetime, 12> BillingTotal INTEGER, 13> TermsID INTEGER, 14> BillingDueDate datetime , 15> PaymentTotal INTEGER, 16> CreditTotal INTEGER 17> 18> ); 19> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> select * from Billings WHERE BillingTotal < 5000 AND BillingDate <= "2002-07-01" 3> GO BankerID BillingNumber BillingDate BillingTotal TermsID BillingDueDate PaymentTotal CreditTotal
------------- ----------------------- ------------ ----------- ----------------------- ------------ -----------
2 2 2001-02-21 00:00:00.000 165 1 2002-02-22 00:00:00.000 123 321
4 4 1999-03-12 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
5 5 2000-04-23 00:00:00.000 165 1 2005-04-17 00:00:00.000 123 321
6 6 2001-06-14 00:00:00.000 165 1 2005-04-18 00:00:00.000 123 321
(4 rows affected) 1> 2> drop table Billings; 3> GO</source>
