sql - I Need some sort of Conditional Join -

sql - I Need some sort of Conditional Join -

okay, know there few posts discuss this, problem cannot solved conditional statement on bring together (the mutual solution).

i have 3 join statements, , depending on query parameters, may need run combination of three. bring together statement quite expensive, want bring together when query needs it, , i'm not prepared write 7 combination if..else.. statement fulfill combinations.

here i've used solutions far, of these have been less ideal:

left bring together joinedtable jt on jt.somecol = somecol jt.somecol = conditions or @neededjoin null

(this expensive, because i'm performing bring together when don't need it, not evaluating join)

outer apply (select top(1) * joinedtable jt jt.somecol = somecol , @neededjoin null)

(this more expensive left joining)

select @sql = @sql + ' inner bring together joinedtable jt ' + ' on jt.somecol = somecol ' + ' (conditions...) '

(this 1 ideal, , how written now, i'm trying convert away dynamic sql).

any thoughts or help great!

edit: if take dynamic sql approach, i'm trying figure out efficient regards structuring query. given have 3 optional conditions, , need results of them current query this:

if status 1 select db inner bring together status 1 union if status 2 select db inner bring together status 2 union if status 3 select db inner bring together status 3

my non-dynamic query task performing left joins:

select db left bring together status 1 left bring together status 2 left bring together status 3 status 1 true or status 2 true or status 3 true

which makes more sense do? since of code "select db" statement same? appears union status more efficient, query long because of it....

thanks!

the dynamic sql solution best in respects; trying run different queries different numbers of joins without rewriting query different numbers of joins - , doesn't work in terms of performance.

when doing sort of stuff æon or ago (say 90s), language used i4gl , queries built using build statement. used generate part of clause, (based on user input), filter criteria generated might like:

a.column1 between 1 , 50 , b.column2 = 'abcd' , c.column3 > 10

in days, didn't have modern bring together notations; i'm going have improvise bit go. typically there core table (or set of core tables) part of query; there tables optionally part of query. in illustration above, assume 'c' alias main table. way code worked be:

note table 'a' referenced in query: add 'fulltablename a' clause add bring together status 'and a.join1 = c.join1' clause note table 'b' referenced... add bits clause , clause. assemble select statement select-list (usually fixed), clause , clause (occasionally decorations such grouping by, having or order too).

the same basic technique should applied here - details different.

first of all, don't have string analyze; know other circumstances tables need add together query. so, still need design things can assembled, but...

the select clause select-list fixed. identify tables must nowadays in query because values pulled tables.

the clause consist of series of joins.

one part core query:

from coretable1 c1 bring together coretable2 c2 on c1.joincolumn = c2.joincolumn bring together coretable3 m on m.primarykey = c1.foreignkey

other tables can added necessary:

join auxilliarytable1 on m.foreignkey1 = a.primarykey

or can specify total query:

join (select relevantcolumn1, relevantcolumn2 auxilliarytable1 column1 between 1 , 50)

in first case, have remember add together criterion main clause, , trust dbms optimizer move status bring together table shown. optimizer automatically; poor 1 might not. utilize query plans help determine how able dbms is.

add clause inter-table criteria not covered in joining operations, , filter criteria based on core tables. note i'm thinking in terms of criteria (and operations) rather alternative criteria (or operations), can deal or long careful parenthesize expressions sufficiently.

occasionally, may have add together couple of bring together conditions connect table core of query - not dreadfully unusual.

add grouping by, having or order clauses (or limits, or other decorations).

note need understanding of database schema , bring together conditions. basically, coding in programming language way have think constructing query. long understand , schema, there aren't insuperable problems.

good luck...

sql sql-server sql-server-2008 left-join