sql - Removing the union from a query and converted it into one query? -


i have been told remove union , change top half of query one, have been told easy cannot figure out. have been told include

where red (major colour) = red (silksname) blue (major colour) = blue (silksname) etc

select s.[silks_skey]     [dbo].[silks] s      inner join [dbo].[subcolour] sc on charindex(sc.[subcolour],s.[silksname]) <> 0     inner join [dbo].[majorcolour] mc on sc.[majorcolour] = mc.[majorcolour]      union      select s.[silks_skey], mc.[majorcolour_skey]      [dbo].[silks] s      inner join [dbo].[majorcolour] mc on charindex(mc.[majorcolour],s.[silksname]) <> 0      order s.[silks_skey]

i see no reason why should remove union all query, best option, although move union subquery:

select  s.[silks_skey], c.[majorcolour_skey]     [dbo].[silks] s          inner join         (   select  [colour] = sc.[subcolour], mc.[majorcolour_skey]                [dbo].[subcolour] sc                     inner join [dbo].[majorcolour] mc                          on sc.[majorcolour] = mc.[majorcolour]             union             select  mc.[majorcolour], mc.[majorcolour_skey]                [dbo].[majorcolour] mc          ) c             on charindex(c.[colour], s.[silksname]) <> 0

if have remove union use this:

select  s.[silks_skey], mc.[majorcolour_skey]     [dbo].[silks] s         left join [dbo].[subcolour] sc             on charindex(sc.[subcolour], s.[silksname]) <> 0         inner join [dbo].[majorcolour] mc             on case when sc.subcolour not null , mc.majourcolour = sc.majourcolour 1                     else charindex(mc.[majorcolour], s.[silksname]) <> 0                  end <> 0

but cannot envisage performing better union.

note hve seen answer , opt case in join rather or, case statement shortcircuit when subcolour exists not evaluate second part, whereas or evaluate both.


Comments

Post a Comment

Popular posts from this blog

Eclipse formatter for java ending braces -

i know if it's possible setup eclipse format braces using banner style. we're working code uses banner style formatting , keep consistent within team version control reasons. here's example of banner style formatting not familiar: public class foo { //------------------------ public static void main (string[] args) { boolean condition = true; try { if (condition) { system.out.println ("condition true"); } else system.out.println ("not condition"); } catch (exception ex) { // handle errors } } //------------------------ } update: name style ratliff. experiment java->code style->formatter preferences. "banner style" seems poor use of space given outline view , quick outline functionality.
Read more

ruby - Nesting modules inside of a Rails eninge gem -

what proper syntax nest child modules within parent module being isolate_namespace rails engine gem? # lib/myengine/engine.rb module myengine class engine < rails::engine isolate_namespace myengine # def ... end end for example. parent module myengine , child module blog. myengine share common domain, crud, taggable, searchable, etc, keep gem code dry , isolated main app (myapp), while inheriting isolated namespace , engine. are either of 2 approaches correct? refactor advice? # # lib/myengine/blog.rb module myengine module blog # def ... end end # b # lib/myengine/blog.rb module myengine class engine < rails::engine isolate_namespace myengine module blog # def ... end end end option a. correct, should lib/my_engine/blog.rb . can read more ruby & rails naming conventions here . further, if want put more modules or classes under blog namespace put them in folder lib/my_engine/blog , nest them under myengine::bl...
Read more

php - Why I am getting the Error "Commands out of sync; you can't run this command now" -

the documentation of error mentioned in title says if commands out of sync; can't run command now in client code, calling client functions in wrong order. this can happen, example, if using mysql_use_result() , try execute new query before have called mysql_free_result(). can happen if try execute 2 queries return data without calling mysql_use_result() or mysql_store_result() in between. from here: http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html but in first query not fetching data mysql database, inserting. , in second query getting data database. here code $connection = mysqli_connect("localhost","username","password","tbl_msgs"); if(mysqli_connect_errno($connection)) { die("failed connect mysql: " . mysqli_connect_error()); } $query = "insert users (total_comments, total_views) values ({$total_comments}, {$total_views});"; $query .= "insert msgs (no...
Read more