Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

MSSQL SELECT WITH CASE STATEMENT IN WHERE CLAUSE

Hello,
I'm trying to do something like this :

select table1.A

from table1 , (select X , Y , Z  from table2 WHERE clause condition ) SUB

where table1.B = 0 and table1.C = 1 and

if  sub.X = 1 => table1.Y = SUB.Y

elseif sub.X = 0 => table1.Y not in (select Y  from table2 WHERE clause condition)

else if there is no result in the sub query , no join with sub, return all the result from table1....)

/*******/

in fact I have a table of product. (table1)

and I have another table (table2)  where I managed the right access for a customer.

if there is no entry in table2, no restriction for the customer in table1 (last else)

if there is an entry in table2, there is a restriction for the customer for  specific product.

there is 2 type of restriction, in the X column (bit).

0 = no access for this product (but yes access to other product)
1 = only access for this product  (so no access for other product)

Many thanks for your help.
0
bruno_boccara
Asked:
bruno_boccara
  • 4
1 Solution
 
John_VidmarCommented:
Would've been easier with real table/field names:
SELECT	a.A
from	table1	a
LEFT
JOIN	table2	b	ON	a.Y = b.Y
			AND	b.X = 1
WHERE	a.B = 0
AND	a.C = 1

UNION

SELECT	a.A
from	table1	a
LEFT
JOIN	table2	b	ON	a.Y = b.Y
			AND	b.X = 0
WHERE	a.B = 0
AND	a.C = 1
AND	b.Y IS NULL

Open in new window

0
 
bruno_boccaraAuthor Commented:
In fact, I made this query:

SELECT DISTINCT E.COL_ED 

FROM EDITEUR E , (SELECT COL_ED , BLOCK_TYPE , CTNUM , PLATFORME FROM RE_ED_BLOCK REB WHERE CTNUM = '0000008909' AND PLATFORME = 'INTRANET') SUB

WHERE Interne = 0 AND ED_ACTUEL = 1

AND (

(SUB.BLOCK_TYPE = 1 AND E.COL_ED = SUB.COL_ED)   /* IT WORKS */

OR

(SUB.BLOCK_TYPE = 0 AND E.COL_ED NOT IN (SELECT COL_ED FROM RE_ED_BLOCK REB WHERE CTNUM = SUB.CTNUM AND PLATFORME = SUB.PLATFORME ))  /* IT WORKS */

 OR (NOT EXISTS(SELECT COL_ED FROM RE_ED_BLOCK REB WHERE CTNUM = SUB.CTNUM AND PLATFORME = SUB.PLATFORME) AND E.COL_ED = E.COL_ED)  /* IT DOESN'T WORKS */
)

Open in new window


the third OR condition do not work.  I want to avoid a join with SUB table if there is no entry and to return all entries from EDITEUR  (with only condition Interne = 0 AND ED_ACTUEL = 1 )

Many thanks for any help.
0
 
bruno_boccaraAuthor Commented:
Hello John,
I can't make a left outer join between table1 (EDITEUR) and table2 (RE_ED_BLOCK).
0
 
bruno_boccaraAuthor Commented:
One way is to do this with INTERSERCT or EXCEPT, but I have a problem when there is no entry in the second table (RE_ED_BLOCK ). I can't return in this case entries from table1 (EDITEUR )
0
 
bruno_boccaraAuthor Commented:
this could be a solution if I could make a left outer join between the two table.

anyway, I found a different way , using fucntion, intersect and except....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now