I am writing a simple query involving three tables (see below). I
would like to display all customers along with their industry
regardless of whether or not the customer has been assigned an
industry.
Table 'Customers' has 426 unique rows
Table 'CustomerAttribute' is linked with/to 'Customers' where
Customers.custid = CustomerAttribute.custid
Table 'Industry' is linked with/to 'CustomerAttribute' where
CustomerAttribute.industryid = Industry.industryid. Furthermore,
CustomerAttribute.industryid is defined where
CustomerAttribute.attributeid = 6
The following query returns 214 row, ie. not what I need:
select cust.code, cust.name, I.code, I.Name
from customer cust left join customerAttribute CA on Cust.custid =
CA.custid
left join Industry I on CA.industryID = I.industryID
where CA.attributeid = 6
Order by cust.name
The following two queries returns 426 row, ie what I want. Why is
this? Better yet, does anyone have a good resource on these subtleties
with MS SQL?(as far as I am concerned the queries below make sense but
I have never had to use this notation)
select cust.code, cust.name, I.code, I.Name
from customer cust left join (select * from customerAttribute where
attributeid=6) CA on Cust.custid = CA.custid
left join Industry I on CA.industryID = I.industryID
Order by cust.name
select cust.code, cust.name, I.code, I.Name
from customer cust left join customerAttribute CA on (Cust.custid =
CA.custid AND CA.attributeid = 6)
left join Industry I on CA.industryID = I.industryID
Order by cust.name
|
