July 23, 2015 at 7:24 am
tableA(colA1 varchar(10),colA2 varchar(10),colA3 varchar(10),colA4 varchar(10),colA5 varchar(10))
tableB(colB1 varchar(10),colB2 varchar(10))
create view vw_main
as
select colA1, colA2, colA3, colA4, colA5, colB2
from tableA A inner join tableB B on A.ColA1 = ColB1
where colA4 = 'case'
now i have second set of records in tableA which irrespective of join with tableB, should come additionaly to the view output. These are with criteria colA4 = 'ifelse'. So i want to get both the records (with current view plus records satisfying criteria colA4 = 'ifelse'), what should be best approach ? i can simply make a UNION like following but if both of those tables contain records in millions and select range is not just 6 columns but around 25-30 columns, would it be good ?
create view vw_main
as
select colA1, colA2, colA3, colA4, colA5, colB2
from tableA A inner join tableB B on A.ColA1 = ColB1
where colA4 = 'case'
UNION
select colA1, colA2, colA3, colA4, colA5, NULL
from tableA A
where colA4 = 'case'
and colA4 = 'ifelse'
July 23, 2015 at 7:35 am
Union all if there's no overlap or if duplicates are OK. Union if you want to eliminate duplicates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 7:36 am
Something like this?
create view vw_main
as
select colA1, colA2, colA3, colA4, colA5, colB2
from tableA A inner join tableB B
on (A.ColA1 = ColB1 AND colA4 = 'case') OR colA4 = 'ifelse'
John
July 23, 2015 at 7:39 am
John Mitchell-245523 (7/23/2015)
Something like this?
create view vw_mainas
select colA1, colA2, colA3, colA4, colA5, colB2
from tableA A inner join tableB B
on (A.ColA1 = ColB1 AND colA4 = 'case') OR colA4 = 'ifelse'
John
Careful, that's going to give a partial cross join. A row in TableA which has colA4 = 'ifelse' will match EVERY row in tableB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 7:44 am
Yikes! Yes, of course. Thanks Gail.
John
July 23, 2015 at 7:50 am
sqlnaive (7/23/2015)
colA4 = 'case'and colA4 = 'ifelse'
I'm going to assume there's a mistake here, as a single column can't be equal to two values at the same time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2015 at 8:36 am
yes Gail. It should be:
colA4 = 'case'
and colA5 = 'ifelse'
choosing between union vs union all, i have only option of Union as a record can be in either of the data sets or in both. Wanted to know if there is any better approach other than using UNION.
July 23, 2015 at 11:18 am
Try:
select colA1, colA2, colA3, colA4, colA5, colB2
from tableA A
left join tableB B on A.ColA1 = ColB1
where
colA4 = 'case' and
(ColB1 is not null or ColA5 = 'ifelse')
Hope this helps.
July 24, 2015 at 5:08 am
Thanks Imex. It works wonderful. With test data it works wonderful plus it's way efficient than UNION. Secondly with UNION, i was getting duplicate records if it's in both data sets. Now will just test it with real data and see how's the performance as well as result.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply