July 31, 2008 at 4:00 am
I have three tables in which i am trying to retrieve orders that have a shipping address which is different to the billing address. Will an inner join satisfy this requirement?
July 31, 2008 at 4:07 am
What are the three tables? How are they related?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 4:20 am
The tables are linked by OrderID
July 31, 2008 at 4:24 am
and...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 4:31 am
FROM T_OrderHeader LEFT OUTER JOIN
T_Payments ON T_OrderHeader.OrderID = T_Payments.OrderID INNER JOIN
T_Customers ON T_OrderHeader.CustomerID = T_Customers.CustomerID
I want pull out all records where the billing address and shipping are different, as well as the card number used for those orders on different orders.
T_Payments and T_Customers have the card number column
T_OrderHeader and T_Payments have the OrderID columns
while T_OrderHeader has the shipping address and T_Customers has the billing address
July 31, 2008 at 4:42 am
Something like this?
SELECT h.OrderID, h.address, c.address, p.cardno, c.cardno
FROM T_OrderHeader h
INNER JOIN T_Customers c ON c.CustomerID = h.CustomerID
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
WHERE h.address <> c.address
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 5:05 am
Thanks Chris
It worked and pulled out accurate results, was also wandering if it will be possible to pull out records of orders made by the same card within a 3 - 7 day period?
August 1, 2008 at 2:59 am
Yes of course! Can you name the date column (and table) which you would like to use for this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 3:22 am
T_Payments has the card number column, while T_OrderHeader has the OrderDate column
August 1, 2008 at 3:34 am
As a separate query to the one above, or built into it? Also, card number is in the customer table, are you sure you want to use card number from the payments table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 3:37 am
Built into it.
August 1, 2008 at 3:52 am
Untested, of course...
SELECT h.OrderID, h.OrderDate, h.address, c.address, p.cardno, c.cardno
FROM T_OrderHeader h
INNER JOIN T_Customers c ON c.CustomerID = h.CustomerID
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
INNER JOIN (SELECT h.OrderID, h.OrderDate, p.cardno
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
) d ON d.cardno = p.cardno
AND d.OrderID <> h.OrderID
AND ABS(DATEDIFF(dd, d.OrderDate, h.OrderDate)) BETWEEN 3 AND 7
WHERE h.address <> c.address
ORDER BY p.cardno, h.OrderIDID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 3:54 am
Hi Chris
The reason why I wanted the card number from the payment table is because the card number column in the customers table has no records
August 1, 2008 at 4:42 am
Hello Chris
What if I want to separate it: will it be like this?
SELECT hr.OrderID, hr.OrderDate, p.CCNumber
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments
ON (p.OrderID = h.OrderID d
ON d.CCNumber = p.CCNumber
AND d.OrderID <> h.OrderID
AND ABS(DATEDIFF(dd, d.OrderDate, T_OrderHeader.OrderDate) BETWEEN 3 AND 7
August 1, 2008 at 5:15 am
Not really, you've lost the derived table d.
If you're comparing values from different rows of a table, then you have to join the table in more than once. You're depending upon values from two different tables which you have to join to bring OrderID, OrderDate, and cardno onto the same row, like this:
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID Then you have to compare this against a second "table" constructed in the same way:(SELECT h.OrderID, h.OrderDate, p.cardno
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
) d
So something like this will work:
SELECT h.OrderID, h.OrderDate, h.address, p.cardno
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
INNER JOIN (SELECT h.OrderID, h.OrderDate, p.cardno
FROM T_OrderHeader h
LEFT OUTER JOIN T_Payments p ON p.OrderID = h.OrderID
) d ON d.cardno = p.cardno
AND d.OrderID <> h.OrderID
AND ABS(DATEDIFF(dd, d.OrderDate, h.OrderDate)) BETWEEN 3 AND 7
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply