Ask a Question
or
Create a Poll
  1. Home /
  2. Questions /
  3. Computers
  4. / Computer Software
  5. / Database Software

by Yoga on October 15th, 2007

Yoga

Question

Help answer this question below.

Want to use a JOIN Statement within CASE Statement in SQL Server 2005. Eg: select x,y,z From A Case ... JOIN B End Something like this. Need a correct sysntax with example please.

Share:
Other

Answers. 3 helpful answers below.

  • by Mabbj747 on January 12th, 2009

    Mabbj747

    Though there is no way you can join to different table on basis of different criteria but I think you can try this

    SELECT
    *
    FROM
    tbTest1
    WHERE
    0 = CASE WHEN SELECT * FROM tbTest2 WHERE x = y THEN 0 ELSE 1
    OR
    0 = CASE WHEN SELECT * FROM tbTest3 WHERE x = z THEN 0 ELSE 1

    No comments. Post one | Permalink

  • by Athmanathan on November 7th, 2008

    Athmanathan

    IF (@ZeroActivity = 1)

    BEGIN

    SELECT i.Store_ID, i.ItemNum, i.ItemName, i.Dept_ID, i.In_Stock, i.Cost, i.Price, i.Retail_Price

    FROM (Inventory i LEFT JOIN Inventory_In n

    ON i.Store_ID = n.Store_ID AND i.ItemNum = n.ItemNum AND (n.[DateTime] >= '3/1/2008' OR n.[DateTime] IS NULL)

    WHERE i.ItemType IN (0,13) AND i.ISDeleted=0 AND i.InActive=0 AND i.Store_Id in (N'1') AND i.Dept_Id IN (N'$1 scoop')

    END

    ELSE

    BEGIN

    SELECT i.Store_ID, i.ItemNum, i.ItemName, i.Dept_ID, i.In_Stock, i.Cost, i.Price, i.Retail_Price

    FROM (Inventory i INNER JOIN Inventory_In n

    ON i.Store_ID = n.Store_ID AND i.ItemNum = n.ItemNum AND (n.[DateTime] >= '3/1/2008' OR n.[DateTime] IS NULL)

    WHERE i.ItemType IN (0,13) AND i.ISDeleted=0 AND i.InActive=0 AND i.Store_Id in (N'1') AND i.Dept_Id IN (N'$1 scoop')

    END



    To reduce the number of lines and maintainability, I modified the above SQL Query to,



    SELECT i.Store_ID, i.ItemNum, i.ItemName, i.Dept_ID, i.In_Stock, i.Cost, i.Price, i.Retail_Price

    FROM (Inventory i LEFT JOIN Inventory_In n

    ON i.Store_ID = n.Store_ID AND i.ItemNum = n.ItemNum AND (n.[DateTime] >= '3/1/2008' OR n.[DateTime] IS NULL)

    WHERE i.ItemType IN (0,13) AND i.ISDeleted=0 AND i.InActive=0 AND i.Store_Id in (N'1') AND i.Dept_Id IN (N'$1 scoop')

    AND (CASE WHEN @ZeroActivity = 1 THEN 1

    ELSE (CASE WHEN i.Store_ID = n.Store_ID AND i.ItemNum = n.ItemNum THEN 1 ELSE 0 END) END)=1

    No comments. Post one | Permalink

  • by King Dina on October 15th, 2007

    King Dina

    I'm not sure if that is possible. I wanted to do something like this once in SQL Server 2000 and learnt that a CASE statement can be applied only in the SELECT part of the query and not the FROM or WHERE part.

    You'll have to look at other options like dynamic queries to achieve this...

    No comments. Post one | Permalink

Want to attach an image to your answer? Click here.

Did this answer your question? If not, then ask a new question or create a poll.

More Questions. Additional questions in this category.

You're reading Want to use a JOIN Statement within CASE Statement in SQL Server 2005. Eg: select x,y,z From A Case ... JOIN B End Something like this. Need a correct sysntax with example please.

Follow us on Facebook!

Related Ads

Related Questions

Professionally Researched

ANSWERBAG BUZZ

Case join
Join case statement
Sql case statement in join
Case when join
Sql case in join statement

Answerbag

a Demand Media company