October 24, 2012 at 8:50 am
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?
Thanks
Wayne
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%')
October 24, 2012 at 8:58 am
[Answer removed. I need to read the question better next time - the other answers have the detail I missed!]
October 24, 2012 at 9:00 am
Hi
This should help
SELECT 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 AND (a3.ORDPROD LIKE 'OTHER%')
WHERE
(a1.CITY = 'THIS')
AND (a2.STATUS = 'THAT')
You don't need the TOP 100 PERCENT for this
You had the JOINS right however you WHERE clause turned the LEFT JOIN to an INNER join
Hope this helps
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
October 24, 2012 at 9:01 am
This: -
AND (a3.ORDPROD LIKE 'OTHER%')
breaks your outer join and turns it into an inner join.
Try this: -
SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]
FROM dbo.TABLE1 a1
INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID
LEFT OUTER JOIN (SELECT CUSTID, ORDPROD
FROM dbo.TABLE3
WHERE ORDPROD LIKE 'OTHER%') a3 ON a1.CUSTID = a3.CUSTID
WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT';
or this: -
SELECT a1.CUSTID, a1.COMPANY AS [Co Name], a2.STATUS AS [TYPE], a3.ORDPROD AS [Product]
FROM dbo.TABLE1 a1
INNER JOIN dbo.TABLE2 a2 ON a1.CUSTID = a2.CUSTID
LEFT OUTER JOIN dbo.TABLE3 a3 ON a1.CUSTID = a3.CUSTID AND a3.ORDPROD LIKE 'OTHER%'
WHERE a1.CITY = 'THIS' AND a2.STATUS = 'THAT'
ah, too slow :hehe:
October 24, 2012 at 9:16 am
Cheers guys
That worked a treat.
I've been coming back to this since yesterday. I wanted to work it out myself, but I think I could have been there another month and still not considered it went before the "Where"
Seems I have some new learning to do
I was a bit disappointed that when I ran it through to create a view, it was not corrected correctly at that point
still, can't have everything I s'pose 😉
thanks again
Wayne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply