Filter Table Before Applying Right Join ON 3RD TABLE

Question

Using MSSQL: Just for clarification

Customer Table

CustomerNumber Name
===================

1              David
2              Thomas
3              Mangold    
4              Issac
------------------------------------------------------------

CustomerAddress Table

CustomerNumber State EffectiveDate
==================================

1              AL    01/01/2017
1              VA    06/01/2017
1              GA    02/01/2018 
1              FL    10/01/2018
2              TX    01/01/2017
3              MA    
4              IL    04/01/2015

SalesOrder Table

CUSTOMER ORDERNo OrderDate
========================

1        1000   03/01/2017
2        1001   10/10/2017
1        1002   11/01/2017
3        1003   12/01/2017
4        1004   01/01/2018
1        1005   02/01/2018
1        1006   01/01/2019

I need to fetch all the orders with the customer detail and the customer address on the order date.

SELECT T1.ORDERNo, T1.ORDERDATE, T1.CUSTOMER, T2.NAME, T3.STATE     
FROM SALESORDER  T1,  CUSTOMER T2, CUSTOMERADDRESS T3 
RIGHT JOIN(
   SELECT CUSTOMER, MAX(EFFECTIVEDATE) FROM CUSTOMERADDRESS
   --WHERE EFFECTIVEDATE <= T1.ORDERDATE
   GROUP BY CUSTOMER)T4 
   ON T3.CUSTOMER = T4.CUSTOMER AND T3.EFFECTIVEDATE=T4.EFFECTIVEDATE
WHERE T1.CUSTOMER = T2.CUSTOMERNUMBER
AND T1.CUSTOMER = T3.CUSTOMERNUMBER

Want to see how to do compare in the join where i compare first table to the 3rd table in the join. see the commented code --WHERE EFFECTIVEDATE <= T1.ORDERDATE

If I remove the commented code, the table in the join cannot reference a table outside.

The expected output is:

 CUSTOMER ORDERNo       OrderDate     CustomerName State
 =============================================

 1        1000          03/01/2017    David        AL
 2        1001          10/10/2017    Thomas       TX
 1        1002          11/01/2017    David        VA
 3        1003          12/01/2017    Mangold      MA
 4        1004          01/01/2018    Issac        IL
 1        1005          02/01/2018    David        GA
 1        1006          01/01/2019    David        FL

The tables in sql fiddle http://sqlfiddle.com/#!18/9eecb:

            CREATE TABLE Customer
                ('CustomerNumber' int, 'CustomerName' varchar(30))
            ;

            INSERT INTO Customer
                ('CustomerNumber', 'CustomerName')
            VALUES
                (1, 'David'),
                (2, 'Thomas'),
                (3, 'Mangold'),
                (4, 'Issac')
            ;

            CREATE TABLE CustomerAddress
                ('CustomerNumber' int, 'State' varchar(2), 'EffectiveDate' date)
            ;

            INSERT INTO CustomerAddress
                ('CustomerNumber', 'State', 'EffectiveDate')
            VALUES
                (1, 'AL', 01/01/2017),
                (1, 'VA', 06/01/2017),
                (1, 'GA', 02/01/2018), 
                (1, 'FL', 10/01/2018),
                (2, 'TX', 01/01/2017),
                (3, 'MA',),   
                (4, 'IL', 04/01/2015)
            ;

            CREATE TABLE SalesOrder 
                ('CUSTOMER' int, 'ORDERNO' int,  'OrderDate' Date)
            ;

            INSERT INTO SalesOrder
                ('CUSTOMER', 'ORDERNO', 'OrderDate')
            VALUES
                (1,        1000,   03/01/2017),
                (2,        1001,   10/10/2017),
                (1,        1002,   11/01/2017),
                (3,        1003,   12/01/2017),
                (4,        1004,   01/01/2018),
                (1,        1005,   02/01/2018),
                (1,        1006,   01/01/2019)
            ;

            CREATE TABLE CustomerAddress
                (`CustomerNumber` int, 'State' varchar(2), `EffectiveDate` date)
            ;

            INSERT INTO CustomerAddress
                (`CustomerNumber`, `State`, 'EffectiveDate')
            VALUES
                (1, 'AL', 01/01/2017),
                (1, 'VA', 06/01/2017),
                (1, 'GA', 02/01/2018), 
                (1, 'FL', 10/01/2018),
                (2, 'TX', 01/01/2017),
                (3, 'MA',),   
                (4, 'IL', 04/01/2015)
            ;

            CREATE TABLE SalesOrder 
                (`CUSTOMER` int, 'ORDERNO' int,  `OrderDate` Date)
            ;

            INSERT INTO SalesOrder
                (`CUSTOMER `, `ORDERNO`, 'OrderDate')
            VALUES
                (1,        1000,   03/01/2017),
                (2,        1001,   10/10/2017),
                (1,        1002,   11/01/2017),
                (3,        1003,   12/01/2017),
                (4,        1004,   01/01/2018),
                (1,        1005,   02/01/2018),
                (1,        1006,   01/01/2019)
            ;

'sql server version'

            CREATE TABLE Customer
                (CustomerNumber int, CustomerName varchar(30))
            ;


            INSERT INTO Customer
                (CustomerNumber, CustomerName)
            VALUES
                (1, 'David'),
                (2, 'Thomas'),
                (3, 'Mangold'),
                (4, 'Issac');               
            ;

            CREATE TABLE CustomerAddress
                (CustomerNumber int, State varchar(2), EffectiveDate date)
            ;

            INSERT INTO CustomerAddress
                (CustomerNumber, State, EffectiveDate)
            VALUES
                (1, 'AL', '01/01/2017'),
                (1, 'VA', '06/01/2017'),
                (1, 'GA', '02/01/2018'), 
                (1, 'FL', '10/01/2018'),
                (2, 'TX', '01/01/2017'),                    
                (4, 'IL', '04/01/2015')
            ;

            INSERT INTO CustomerAddress
                (CustomerNumber, State)
            VALUES
                (3, 'MA' )
            ;

            CREATE TABLE SalesOrder 
                (CUSTOMER int, ORDERNO int,  OrderDate Date)
            ;

            INSERT INTO SalesOrder
                (CUSTOMER, ORDERNO, OrderDate)
            VALUES
                (1,        1000,   '03/01/2017'),
                (2,        1001,   '10/10/2017'),
                (1,        1002,   '11/01/2017'),
                (3,        1003,   '12/01/2017'),
                (4,        1004,   '01/01/2018'),
                (1,        1005,   '02/01/2018'),
                (1,        1006,   '01/01/2019')
            ;

The problem: Need to Pick all the Sales Orders and their customer Name, and the Customer Address. The important and tricky part is the customer address changes based on the date of the sales order.

--MODIFIED VERSION OF THE INCOMPLETE QUERY

        SELECT T1.ORDERNo, T1.ORDERDATE, T1.CUSTOMER, T2.CustomerName, T3.STATE     
        FROM   CUSTOMER T2, SALESORDER  T1 INNER JOIN CUSTOMERADDRESS T3 ON T1.CUSTOMER = T3.CUSTOMERNUMBER
        RIGHT JOIN(
           SELECT CustomerNumber, MAX(EFFECTIVEDATE) as EffectiveDate4 FROM CUSTOMERADDRESS 
           --WHERE EFFECTIVEDATE < T1.ORDERDATE  
           GROUP BY CustomerNumber 
           --HAVING EFFECTIVEDATE < T1.ORDERDATE
           ) T4
           ON T3.CustomerNumber = T4.CustomerNumber AND T3.EFFECTIVEDATE=T4.EffectiveDate4
        WHERE T1.CUSTOMER = T2.CUSTOMERNUMBER

That query won't work anyway, `ORDER` is a reserved keyword and the object name is unquoted. Personally, I suggest using an object name that isn't a keyword, they only cause problems. — Thom A, Feb 28 '19 at 16:26
First things first, time to start using ANSI-92 style joins. They have been available for more than 25 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins Also, use aliases that have some kind of meaning, T1, T2 is not a good idea. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 Also, you should avoid using reserved names for objects. "Order" is a poor name for a table. — Sean Lange, Feb 28 '19 at 16:27
Changed Order to SalesOrder. T1, T2 alias is just for convenience. — Surya The Sun, Feb 28 '19 at 16:30
I suspect this could be greatly simplified but not really sure what you are trying to do here. What are you expecting for output? — Sean Lange, Feb 28 '19 at 16:30
Aliases are convenient, but generic aliases are not. They make things harder because you have to constantly refer back to the alias to figure out what the alias means. In your query instead of T1, T2, T3 I would use something like so, c and ca — Sean Lange, Feb 28 '19 at 16:32
I need to fetch all sales order, relevant customer name and address. Here the SalesOrder 1003 to customer 3, (dont have a effectivedate in the customeraddress) should be listed as well. — Surya The Sun, Feb 28 '19 at 16:33
As currently posted, customeraddress is irrelevant since you include no columns from that table. So start over. Your query and your stated goal simply do not match. Understand your goal BEFORE you start writing a query. Write the query in steps. And a right join to address is not part of your solution - that is a guarantee. — SMor, Feb 28 '19 at 16:36
Given the sample data here, which does not include any address information. What are you trying to accomplish with the right join? — Sean Lange, Feb 28 '19 at 16:39
included T3.STATE in the select statement. Just select the state (didnt include the whole address info for simplicity) — Surya The Sun, Feb 28 '19 at 16:39
Before you post please google error messages & read many answers for many reasons. You will find that problem is a faq. You probably want CROSS JOIN not comma. Comma means cross join but at lower precedence than joins with keyword JOIN. So don't mix them, it's confusing. Since the JOIN is done first, T1 is unknown there. When you post code, give a [mcve]. This code is not minimal, you could chop it way down to isolate the problem. — philipxy, Feb 28 '19 at 18:58
Learn what left/right join on returns: inner join on rows plus unmatched left/right table rows extended by nulls. Always know what inner join you want as part of an outer join. A where that requires a right/left [sic] table column to be not null after a left/right join on removes any rows extended by nulls, ie leaves only inner join on rows, ie "turns outer join into inner join". You have that. That is also a faq. Also for a [mcve] to be mininmal you should add minimal problem code to working code. So give code that you show does what you expect, to find the first place you go wrong. — philipxy, Feb 28 '19 at 19:03
Re more problems: Your description of what you want is so terse that it is unclear. But if you want an outer joih to hold all orders then a table with all orders has to be the left table of a left join or the right table of a right join. — philipxy, Mar 01 '19 at 09:11
@philipxy The 3 model tables with the expected output is there, you can phrase your query in your own fashion. I was trying to do a outer join T4 on T1 and T3 2 different tables, where as join goes with only one table. — Surya The Sun, Mar 01 '19 at 10:44
Your last comment is also "so terse that it is unclear". Use enough words, sentences & references to parts of examples to say exactly what you mean. Please act on my past comments: use CROSS JOIN instead of comma in FROM (I told you, you are now asking for `t1 cross join t2 cross join (t3 right join t4 on c)`; give small code that you show does what you expect and code that is that code extended by small problem code; use words to clearly say what you want; clarify via edit not comment. — philipxy, Mar 01 '19 at 23:42
Example tables, expected output, Incomplete are there. Form your own query with JOIN, comma etc. I will explain the problem a bit more here. Need to Pick all the Sales Orders and their customer Name, and the Customer Address. the important and tricky part is the customer address changes based on the date of the sales order.I will explain the problem a bit more here. Need to Pick all the Sales Orders and their customer Name, and the Customer Address. the important and tricky part is the customer address changes based on the date of the sales order. — Surya The Sun, Mar 02 '19 at 11:25
This is not clear, I can't tell what code is supposed to do what, or what code runs or doesn't run with what results, or what code ran the way you expected, or what is the smallest code bigger than that with a problem. "Use enough words, sentences & references to parts of examples to say exactly what you mean." "Please clarify via edits, not comments." Etc etc. Please act on all my comments. (Suggest you stop commenting out code.) PS Google 'stackexchange notifications" to learn how to use @x in a comment to notify a non-poster non-sole commenter of a comment. — philipxy, Mar 04 '19 at 20:54

score 0 · Answer 1 · answered Feb 28 '19 at 17:29

0

OUTER APPLY should solve your problem. based on your needs you can change your query.

SELECT T1.ORDERNo, T1.ORDERDATE, T1.CUSTOMER, T2.NAME, T3.STATE     
FROM SALESORDER  T1,  CUSTOMER T2, CUSTOMERADDRESS T3 
OUTER APPLY(
   SELECT CUSTOMER, MAX(EFFECTIVEDATE) FROM CUSTOMERADDRESS
   WHERE EFFECTIVEDATE <= T1.ORDERDATE
   AND T3.CUSTOMER = CUSTOMER )T4 
WHERE T1.CUSTOMER = T2.CUSTOMERNUMBER
AND T1.CUSTOMER = T3.CUSTOMERNUMBER
AND T3.EFFECTIVEDATE = T4.EFFECTIVEDATE

answered Feb 28 '19 at 17:29

Krishna

529
3
13

Thanks. EFFECTIVEDATE <= T1.ORDERDATE line errors out as multi-part identifier could not be found. – Surya The Sun Feb 28 '19 at 18:59
http://sqlfiddle.com/#!18/9eecb Creating the tables to play around with the sql. – Surya The Sun Feb 28 '19 at 19:10
1

expand your query to write proper joins then try it again. this only works in MS SQL Server – Krishna Feb 28 '19 at 19:30

Filter Table Before Applying Right Join ON 3RD TABLE

Customer Table

CustomerAddress Table

SalesOrder Table

1 Answers1