There are the two tables `Client` and `Stock`:

```
Table Client
    Column IDC       (primary key, int, not null)

Table Stock
    Column IDS       (primary key, int, not null)
    Column IDC       (int, not null)
    Column Type      (bit, not null)
    Column Issued    (bit, not null)
    Column Price     (decimal(10,2), null)

```

They are to be taken as connected by `Client.IDC = Stock.IDC`.

Discussion [here](https://stackoverflow.com/questions/48943585/sql-exclude-values-of-a-column-which-meet-a-requirement-at-least-once) shows how to split `IDC` into the following two groups:

1. `IDC` with `Type = 1` and `Price not NULL`, and
2. Remaining `IDC`, i.e. `IDC` which have no row with `Type = 1`
and `Price not NULL` at all

For the second group, is it feasible to return `IDC` together with `Price` in case of `Issued = 1`? The desired output should contain `IDC` for which no row with `Type = 1` and `Price not NULL` exists, but for which rows with `Issued = 1` and `Price not NULL` are available.

Example:

```
Table Client
    IDC
    1
    2
    3
    4
    5
    6
    7
    8
    9

Table Stock
    IDS  IDC  Type  Issued  Price

    1    1    1     0       20
    2    1    0     1       50
    3    3    1     0       NULL
    4    3    0     1       90
    5    4    1     0       10
    6    4    0     0       70
    7    5    1     0       NULL
    8    5    0     0       30
    9    6    0     0       40
    10   6    0     1       80
    11   7    0     1       NULL
    12   8    1     1       60
    13   9    1     1       NULL

Desired return [IDC, Price]: [3,90], [6,80]

```

My attempt is not successful:

```
SELECT C.IDC, S.Price
FROM Client C LEFT JOIN Stock S
ON C.IDC = S.IDC
    AND S.Type = 1
    AND S.Price IS NOT NULL
WHERE
    S.IDC IS NULL
    AND S.Issued = 1

```

My assumption is, that the second condition in the `WHERE` clause is made redundant by the first condition in the `WHERE` clause. However, I can't really figure it out. I'm using Microsoft SQL Server 2008.
['https://Stackoverflow.com/questions/48983929', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6198659/']
Is that what you wanna get?

```
SELECT z.IDC, z.PRICE
FROM Stock z
WHERE z.IDC IN 
(
    SELECT c.IDC IDC
    FROM [Client] c
            LEFT JOIN Stock S 
                on C.IDC = S.IDC
                    AND S.Type = 1 
                    AND S.Price IS NOT NULL
        WHERE S.IDC IS NULL
) AND Z.PRICE IS NOT NULL AND z.Issued = 1

```

Yes, it in fact can be written with `Stock` only (without `JOIN` with `Client`):

```
SELECT Z.IDC, Z.Price
FROM Stock Z
WHERE Z.IDC NOT IN
(
    SELECT S.IDC
    FROM Stock S
    WHERE S.Type = 1 AND S.Price IS NOT NULL
)
AND Z.Issued = 1
AND Z.Price IS NOT NULL

```
You may be looking for this 

```
SELECT C.IDC, S.Price
FROM Client C 
LEFT JOIN Stock S  ON C.IDC = S.IDC
WHERE
    (S.IDC IS NULL AND S.Issued = 1) OR 
    (S.Type = 1 AND S.Price IS NOT NULL)

```