query

from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
showing?
CREATE TABLE Customers
(
Cust_ID int PRIMARY KEY,
Cust_Name char(20)
)
CREATE TABLE Orders
(
OrderID int PRIMARY KEY,
Cust_ID int REFERENCES Customers(Cust_ID)
)
GO
INSERT Customers VALUES (1, 'Cust 1')
INSERT Customers VALUES (2, 'Cust 2')
INSERT Customers VALUES (3, 'Cust 3')
INSERT Orders VALUES (10001, 1)
INSERT Orders VALUES (20001, 2)
GO
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
Output:Customers.Cust_ID Cust_Name
Orders.Cust_ID -- -- --
1 Cust 1 12 Cust 2
NULL3 Cust 3 NULLThat is what a LEFT JOIN is for. To select all rows from left side and rows
from the right side that meet the join criteria. If you want to filter then
you should use the WHERE clause.
SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
'Orders.Cust_ID'=Orders.Cust_ID
FROM Customers LEFT JOIN Orders ON Customers.Cust_ID=Orders.Cust_ID
where Customers.Cust_ID <> 2
AMB
"js" wrote:

> from Inside Microsoft SQL Server 2000, why Customers.Cust_ID = 2 is still
> showing?
> CREATE TABLE Customers
> (
> Cust_ID int PRIMARY KEY,
> Cust_Name char(20)
> )
> CREATE TABLE Orders
> (
> OrderID int PRIMARY KEY,
> Cust_ID int REFERENCES Customers(Cust_ID)
> )
> GO
> INSERT Customers VALUES (1, 'Cust 1')
> INSERT Customers VALUES (2, 'Cust 2')
> INSERT Customers VALUES (3, 'Cust 3')
> INSERT Orders VALUES (10001, 1)
> INSERT Orders VALUES (20001, 2)
> GO
> SELECT 'Customers.Cust_ID'=Customers.Cust_ID, Customers.Cust_Name,
> 'Orders.Cust_ID'=Orders.Cust_IDFROM Customers LEFT JOIN Orders ON
> Customers.Cust_ID=Orders.Cust_ID and Customers.Cust_ID <> 2
> Output:Customers.Cust_ID Cust_Name
> Orders.Cust_ID -- -- --
--
> 1 Cust 1 12 Cust 2
> NULL3 Cust 3 NULL
>
>