May 20, 2016 at 12:12 am
hi,
if OBJECT_ID('dbo.act','u') is not null
drop table act
create table act(actid int,actname varchar(50),reportno varchar(50))
insert into act values(1,'whitewash','r1')
insert into act values(2,'whitewash','r2')
insert into act values(3,'whitewash','r3')
insert into act values(4,'paint','r4')
select * from act
if OBJECT_ID('dbo.i','u') is not null
drop table i
create table i(iID int, f decimal(8,2), t decimal(8,2) , actid int)
insert into i values(1,10,20,3)
insert into i values(2,20,30,4)
select * from i
if OBJECT_ID('dbo.j','u') is not null
drop table j
create table j(jid int, pp1id int, pp2id int, actid int)
insert into j values(1,2,3,1)
insert into j values(2,3,4,2)
select * from j
if OBJECT_ID('dbo.pp','u') is not null
drop table pp
create table pp(ppid int,f decimal(8,2))
insert into pp values(1,11.5)
insert into pp values(2,22.7)
insert into pp values(3,34.0)
insert into pp values(4,44.0)
select * from pp
if OBJECT_ID('dbo.pole','u') is not null
drop table pole
create table pole(poleid int,ppid INT,point decimal(8,2))
insert into pole values(1,2,22.7)
insert into pole values(2,3,34.0)
insert into pole values(3,4,44.0)
select * from pole
first col of each table is pk.
I can not change left join , they will be like that because .
there is one to one relation ship between "act" to "i" and "act" to "j" ,but
"i" and "j" only one will have data for each "act" record.
pp table is masters table it has position of pole in a bridge.
pole table is temp table it gets data from pp table which is masters table
, so pole table will have less than or equal to pp table.
act table is header table , when ever any activity is performed on bridge we enter
a record in act table then its actid goes in "i" or "j" table.
columns of table "j" pp1id,pp2id gets data from master table "pp"
note : i do not want to u use union.
select *
from act a
left join j on j.actid=a.actid
left join pp on j.pp1id = pp.ppid
left join pp p1 on j.pp2id = p1.ppid
left join i on i.actid=a.actid
join pole p on p.point between ( case when i.f is not null then i.f else pp.f end)
and ( case when i.t is not null then i.t else p1.f end)
yours sincerly
22
<html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/8afcfa97-c620-4f86-909b-71db985e9d74/should-i-use-derive-table-i-following-or-is-it-correct?forum=transactsql" id="link64_adl_tabid" style="display:none;">24</html:div>
25
May 20, 2016 at 8:05 am
The CASE expression makes your join non-SARGable. You are better off using a UNION. Why do you not want to use UNION?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 20, 2016 at 8:35 am
Can You post sample data?
May 21, 2016 at 1:58 am
hi,
if OBJECT_ID('dbo.act','u') is not null
drop table act
create table act(actid int,actname varchar(50),reportno varchar(50))
insert into act values(1,'whitewash','r1')
insert into act values(2,'whitewash','r2')
insert into act values(3,'whitewash','r3')
insert into act values(4,'paint','r4')
select * from act
if OBJECT_ID('dbo.i','u') is not null
drop table i
create table i(iID int, f decimal(8,2), t decimal(8,2) , actid int)
insert into i values(1,10,20,3)
insert into i values(2,20,30,4)
select * from i
if OBJECT_ID('dbo.j','u') is not null
drop table j
create table j(jid int, pp1id int, pp2id int, actid int)
insert into j values(1,2,3,1)
insert into j values(2,3,4,2)
select * from j
if OBJECT_ID('dbo.pp','u') is not null
drop table pp
create table pp(ppid int,f decimal(8,2))
insert into pp values(1,11.5)
insert into pp values(2,22.7)
insert into pp values(3,34.0)
insert into pp values(4,44.0)
select * from pp
if OBJECT_ID('dbo.pole','u') is not null
drop table pole
create table pole(poleid int,ppid INT,point decimal(8,2))
insert into pole values(1,2,22.7)
insert into pole values(2,3,34.0)
insert into pole values(3,4,44.0)
select * from pole
first col of each table is pk.
I can not change left join , they will be like that because .
there is one to one relation ship between "act" to "i" and "act" to "j" ,but
"i" and "j" only one will have data for each "act" record.
pp table is masters table it has position of pole in a bridge.
pole table is temp table it gets data from pp table which is masters table
, so pole table will have less than or equal to pp table.
act table is header table , when ever any activity is performed on bridge we enter
a record in act table then its actid goes in "i" or "j" table.
columns of table "j" pp1id,pp2id gets data from master table "pp"
note : i do not want to u use union.
select *
from act a
left join j on j.actid=a.actid
left join pp on j.pp1id = pp.ppid
left join pp p1 on j.pp2id = p1.ppid
left join i on i.actid=a.actid
join pole p on p.point between ( case when i.f is not null then i.f else pp.f end)
and ( case when i.t is not null then i.t else p1.f end)
yours sincerly
22
<html:div data-url="https://social.msdn.microsoft.com/Forums/en-US/8afcfa97-c620-4f86-909b-71db985e9d74/should-i-use-derive-table-i-following-or-is-it-correct?forum=transactsql" id="link64_adl_tabid" style="display:none;">24</html:div>
25
May 22, 2016 at 1:10 am
I have been using union at appropriate places, but i wanted to understand this one.
yours sincerely
May 23, 2016 at 3:25 am
Your requirement is not clear to me.
Hope below code will help you to move in the right direction
SELECT *
from act a
inner join
(select jid, pp.f as f1 , p1.f as f2, j.actid
from j
left join pp on j.pp1id = pp.ppid
left join pp p1 on j.pp2id = p1.ppid
UNION all
SELECT iId, f, t, actid
FROM i
)D
ON a.actid = D.actid
inner join pole p
on p.point between f1 and f2
May 23, 2016 at 8:16 am
Providing an exact duplicate of your original message is not going to make your objectives any clearer. Specifically, it is not going to answer the question of why you do not want to use UNION when that is most likely going to be the best approach.
There are only two reasons not to use UNION:
* There is a better approach.
* This is homework and the homework specifies that you can't use UNION.
I suspect the answer is the second one, especially since your response for more details was an exact duplicate of your original question.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 28, 2016 at 2:44 am
it is was not duplicate , i have overwritten the old one, becasue old one had got syntex error ( while typing)
so please consider the question. is the query wrong or right ,
as far as other way of writing is concern , i know other ways. ( i want to know this particular query )
so the question is query is write or wrong ( if there is any syntax error or other logical problems, then pls write to me i will explain)
yours sincerly
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply