Filteration in SQL Query by using Join Query

I have a Complex SQL Query which开发者_如何学Go is written with lot many Joins and Conditions.

ComplexQuery has few columns and most notable column names are WeightCode and DrugName.

[Assumption] : Select * from ComplexQuery.

I have a second Table : Select DrugName from Table2.

My requirement is such a way that,

If WeightCode = 2, Then First Five letters of DrugName from ComplexQuery matching First Five Letters of DrugName from Table 2 should be removed.

I am confused on approaching ..! How should i define in a single join query with filteration condition ??

Make use of Case..When may resolve your issue

Example

SELECT column1, column2
FROM TABLE
WHERE
column1 = 
  CASE @locationType
      WHEN 'val1' THEN column1
      WHEN 'val2' THEN column1 
  END

Note : this is just example

So, if ComplexQuery.WeightCode = 2, and if the first five letters of WeightCode.DrugName can be matched to the first five letters of any record from Table2.DrugName, you want to remove the first five letters from ComplexQuery.WeightCode?

If so, try this:

SELECT cq.*, 
    CASE cq.WeightCode WHEN 2 THEN RIGHT(cq.DrugName, LEN(cq.DrugName)-5) ELSE cq.DrugName END DrugName
FROM ComplexQuery cq
    LEFT OUTER JOIN Table2 t 
        ON cq.WeightCode = 2 AND LEFT(cq.DrugName, 5) = LEFT(t2.DrugName, 5)

Use 2 queries and Union All the results together.

In the first query get the data where WeightCode = 2. Do the join and the substrings to return the results you need.

The second query get the data where WeightCode != 2. Return the same field names and types in the same order.

Put a union all between them to join the results into one result set.

You could also use a subselect with NOT IN or EXISTS.

Make a list of what you want to exclude in the subquery, with a join condition tying it back to the outer query.

I.E.

WHERE MainTableKey NOT IN (SELECT ForeignKey from MyTable2
                           WHERE DoNotInclude = 1)

EDIT:

Alternate solution, use EXCEPT

Pseudocode would be:

SELECT col1, col2, col3
FROM ComplexQuery
EXCEPT
SELECT Col1, Col2, Col3
FROM MyTable t1
INNER JOIN Table2 t2
   ON LEFT(t1.Drugname, 5) = LEFT(t2.drugname, 5)
   AND t1.WeightCode = 2

Except removes a second result set from a first result set. The only catch is you need to return the same fields in both sets, just like in a UNION.