CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
goofyui
goofyui

Reputation: 3492

Filteration in SQL Query by using Join Query

I have a Complex SQL Query which 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 ??

Upvotes: 0

Views: 134

Answers (4)

Pranay Rana
Pranay Rana

Reputation: 176936

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

Upvotes: 2

JNK
JNK

Reputation: 65187

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.

Upvotes: 0

Scott Bruns
Scott Bruns

Reputation: 1981

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.

Upvotes: 0

Michael Ames
Michael Ames

Reputation: 2617

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)

Upvotes: 0

Related Questions