I am struggling to understand joins and hope somebody can help me
I have three tables that I am querying, tables one and two contain fixed customer information where you cannot have an entry for a customer in table1 without it creating a record in table2 and the relationship is always one to one linked by custid which I am comfortable with.
Table3 lists orders placed by the customers, where a customer might have placed multiple orders for a particular product or not ordered at all. I am looking to list all customers in a certain location regardless of whether they have ordered or not with any that have placed multiple orders having multiple entries.
I have written the below query but it does not return the customers in tables1&2 who have not placed an order. I cannot request NULLs as this is for a specific product and table3 contains other products they have purchased.
I am using SQL Server 2008 which we have recently upgraded to
After many unsuccessful attempts, I reverted to writing it as I would in SQL2000, (second query) and that worked returning all customers including those that had not ordered. So I decided to use “create view” with this code and see what happened.
SQL created the view and corrected the syntax to joins, exactly the same as I had written but it no longer includes the customers who had not ordered.
Can anybody help me with what I am doing wrong and what I need to add to include all customers?
SELECT TOP (100) PERCENT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]
FROM dbo.TABLE1 AS a1 INNER JOIN
dbo.TABLE2 AS a2 ON a1.CUSTID = a2.CUSTID LEFT OUTER JOIN
dbo.TABLE3 AS a3 ON a1.CUSTID = a3.CUSTID
WHERE (a1.CITY = 'THIS') AND (a2.STATUS = 'THAT') AND (a3.ORDPROD LIKE 'OTHER%')
SELECT TOP (100) PERCENT a1.custid, a1.COMPANY AS [Co Name],
a2.STATUS AS [TYPE],
a3.ORDPROD AS [Product]
FROM TABLE1 a1, TABLE2 a2, TABLE3 a3
where a1.CUSTID = a2.CUSTID and a1.CUSTID *= a3.CUSTID
and (a1.CITY = 'THIS') AND (a2.STATUS = 'THAT') AND (a3.ORDPROD LIKE 'OTHER%')