August 9, 2016 at 1:50 pm
This may not seem odd to many, but it is the first time I have seen it and I need to rework it in such a way that I can take the result set and put it in a table then use the variables after the insert.
I just don't really understand the structure of these joins. Any help in refactoring or explaining would be awesome ! I apologize for not having any sample data.
***** The joins with the () are whats confusing me.
Thanks !
DECLARE @AsOfDate DATE = GETDATE()
SELECT *
FROM TransactionTable tt
LEFT JOIN (PatientTable pt
INNER JOIN Batch b ON pt.BatchId = b.BatchId ) ON t1.PatientVisitProcsId = pt.PatientVisitProcsId AND b.Entry < DATEADD(d, 1, @AsOfDate)
INNER JOIN Actions A ON tt.TransactionsId = a.TransactionsId AND a.Action <> 'N'
INNER JOIN VisitAction vt ON a.VisitTransactionsId = vt.VisitTransactionsId
INNER JOIN (PaymentMethod pm
INNER JOIN Batch b2 ON pm.BatchId = b2.BatchId) ON vt.PaymentMethodId = pm.PaymentMethodId AND b2.Entry < DATEADD(d, 1, @AsOfDate)
INNER JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId
***SQL born on date Spring 2013:-)
August 9, 2016 at 3:05 pm
thomashohner (8/9/2016)
This may not seem odd to many, but it is the first time I have seen it and I need to rework it in such a way that I can take the result set and put it in a table then use the variables after the insert.I just don't really understand the structure of these joins. Any help in refactoring or explaining would be awesome ! I apologize for not having any sample data.
***** The joins with the () are whats confusing me.
Thanks !
DECLARE @AsOfDate DATE = GETDATE()
SELECT *
FROM TransactionTable tt
LEFT JOIN (PatientTable pt
INNER JOIN Batch b ON pt.BatchId = b.BatchId ) ON t1.PatientVisitProcsId = pt.PatientVisitProcsId AND b.Entry < DATEADD(d, 1, @AsOfDate)
INNER JOIN Actions A ON tt.TransactionsId = a.TransactionsId AND a.Action <> 'N'
INNER JOIN VisitAction vt ON a.VisitTransactionsId = vt.VisitTransactionsId
INNER JOIN (PaymentMethod pm
INNER JOIN Batch b2 ON pm.BatchId = b2.BatchId) ON vt.PaymentMethodId = pm.PaymentMethodId AND b2.Entry < DATEADD(d, 1, @AsOfDate)
INNER JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId
It's no different from any other place that parentheses are used. It changes the logical order of processing. The joins inside parentheses must be (logically) evaluated before joins outside of the parentheses.
Drew
PS: Technically, the parentheses aren't needed, because it's actually the ON clauses which control the order, the parentheses just make it easier for humans to understand the logic that is being used.
PPS: The logical order of processing doesn't have to match the physical ordering of processing as long as the results are guaranteed to be the same.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 9, 2016 at 3:59 pm
To add to drew's answer, if your issue is with that JOIN within a JOIN condition, I would think of it as building a result-set from the inner JOIN, and then using that result-set in the outer JOIN condition.
Here's an example of that type of join being refactored (without the parentheses, which drew has described):
declare @stores table (
store_id int
, store_name nvarchar(500)
)
declare @customers table (
customer_id int
, store_id int
, customer_name nvarchar(500)
)
declare @accounts table (
account_id int
, customer_id int
, account_name nvarchar(500)
, account_credit decimal(12,5)
)
insert into @stores(store_id, store_name)
select 1, 'London'
union all select 2, 'Cambridge'
union all select 3, 'Leeds'
union all select 4, 'Brighton'
union all select 5, 'Oxford'
insert into @customers(customer_id, store_id, customer_name)
select 1, 1, 'Jane Doe'
union all select 2, 1, 'Jim Smith'
union all select 3, 1, 'Michael Jones'
union all select 4, 2, 'Timothy Smith'
union all select 5, 3, 'Kim Peters'
union all select 5, 4, 'Kim Peters'
insert into @accounts(account_id, customer_id, account_name, account_credit)
select 1, 1, 'Savings', 50
union all select 2, 1, 'Cheque', 12
union all select 3, 2, 'Savings', 1
union all select 4, 3, 'Cheque', 120
union all select 5, 4, 'Credit', 73
union all select 6, 4, 'Savings', 52
union all select 7, 5, 'Credit', 63
-- Get the details of all accounts with a balance greater than 50, including any stores that don't have balances.
-- By joining to customers+accounts on the condition that the customer has an account with an account credit of above 50 and the customer's store_id matches stores.store_id
-- This is probably the option I would use, even though it's harder to read.
select
stores.store_id
, stores.store_name
, customers.customer_id
, customers.customer_name
, accounts.account_id
, accounts.account_name
, accounts.account_credit
from @stores as stores
left join @customers as customers
inner join @accounts as accounts
on customers.customer_id = accounts.customer_id
on stores.store_id = customers.store_id
and accounts.account_credit > 50
order by stores.store_id,
customers.customer_id,
accounts.account_id
-- By joining to customers+accounts in a subquery. This is a more common way of doing what has been done above (at least I've seen it more where I work).
select
stores.store_id
, stores.store_name
, customeraccounts.customer_id
, customeraccounts.customer_name
, customeraccounts.account_id
, customeraccounts.account_name
, customeraccounts.account_credit
from @stores as stores
left join
(
select
customers.customer_id,
customers.customer_name,
customers.store_id,
accounts.account_id,
accounts.account_name,
accounts.account_credit
from @customers as customers
inner join @accounts as accounts
on customers.customer_id = accounts.customer_id
where accounts.account_credit > 50
) customeraccounts
on stores.store_id = customeraccounts.store_id
order by stores.store_id,
customeraccounts.customer_id,
customeraccounts.account_id
-- Do the same as above, with filtering done in the WHERE clause
select
stores.store_id
, stores.store_name
, customers.customer_id
, customers.customer_name
, accounts.account_id
, accounts.account_name
, accounts.account_credit
from @stores as stores
left join @customers as customers
on stores.store_id = customers.store_id
left join @accounts as accounts
on customers.customer_id = accounts.customer_id
where accounts.account_credit > 50
or not exists (
select *
from @accounts accounts
where customers.customer_id = accounts.customer_id
)
order by stores.store_id,
customers.customer_id,
accounts.account_id
August 9, 2016 at 4:05 pm
Thanks guys I had just never seen it before and so the syntax threw me. It makes sense now. Thanks !!
***SQL born on date Spring 2013:-)
August 9, 2016 at 4:24 pm
thomashohner (8/9/2016)
Thanks guys I had just never seen it before and so the syntax threw me. It makes sense now. Thanks !!
INNER JOINs and CROSS JOINS are associative, so there's no need to use parens if you only have those types of joins. And in cases where OUTER JOINS are used, they're usually placed last, so that there is no need to change the processing order. Also, some people may use CTEs, derived tables, or views to avoid this type of construct. All of those contribute to the fact that these constructions aren't commonplace.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply