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_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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy