March 31, 2008 at 9:12 am
ok, i wrote this simple query.
I have a salesorderlineitem table. each line can have a status of SLH ( history) or SLI ( invioced).
An order can have many salesOrderLines.
I needed to return all orders with lines in both SLH and SLI.
I know my query works as I have tested it. I am still unsure of the flow diagram that would be associated with this code. Can anyone explain.
create table soli
(
solineitemid int,
partnumber varchar(10),
solineitemstatuscode varchar(3),
orderid int
)
insert into soli
values(1,'asd','SLI',1)
insert into soli
values(2,'asdfa','SLH',1)
insert into soli
values(3,'asd','SLI',1)
insert into soli
values(4,'asd','SLI',1)
insert into soli
values(5,'asd','SLI',2)
insert into soli
values(6,'asd','SLI',2)
insert into soli
values(7,'asd','SLI',3)
insert into soli
values(8,'asd','SLH',3)
select distinct soli1.orderid from soli as soli1
join
(
select orderid from soli as soli2
where solineitemstatuscode = 'SLH'
) as a on soli1.orderid = a.orderid
join
(
select orderid from soli as soli3
where solineitemstatuscode = 'SLI'
) as b on soli1.orderid = b.orderid
Note, only orders 1 and 3 will be returned.
March 31, 2008 at 10:17 am
What I'm going to tell you is exactly what can be derived from looking at the Execution Plan as well:
First, it will execute
select orderid from soli as soli2
where solineitemstatuscode = 'SLH'
and get orders 1 and 3
Then, it will execute
select orderid from soli as soli3
where solineitemstatuscode = 'SLI'
and get the orders 1, 2, and 3 (I'm distinct'ing at this point for brevity, even though more are returned)
Then, according to the standard definition of INNER JOIN, it will take every record from the first execution and try to find a match in the second execution, which will filter out all orders numbered 2
It will then take this, and INNER JOIN to soli1 causing only orders 1 and 3 to remain.
It will then apply the distinct operator to that result set, and return 1 and 3.
Hope this helps.
(and next time, turn on your execution plan 🙂 )
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply