Solved

MSSQL SELECT WITH CASE STATEMENT IN WHERE CLAUSE

Posted on 2014-02-27
5
573 Views
Last Modified: 2014-02-27
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
Comment
Question by:bruno_boccara
  • 4
5 Comments
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39892282
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
 

Author Comment

by:bruno_boccara
ID: 39892295
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
 

Author Comment

by:bruno_boccara
ID: 39892319
Hello John,
I can't make a left outer join between table1 (EDITEUR) and table2 (RE_ED_BLOCK).
0
 

Author Comment

by:bruno_boccara
ID: 39892463
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
 

Author Closing Comment

by:bruno_boccara
ID: 39892547
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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question