Joins?

  • 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%')

  • [Answer removed. I need to read the question better next time - the other answers have the detail I missed!]

  • 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

  • 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'


    --EDIT--

    ah, too slow :hehe:


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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