CODEWITHSUNDEEP
sqlsql-server
Kamil
Kamil

Reputation: 13931

Select from table A depending on some field in table B

I have 

TableA (TableAId int PK, Name varchar) 
TableB (OtherId, TableAId FK, TimeBegin timestamp ,TimeEnd timestamp)

I want to select from TableA Name(s), which have TimeEnd = NULL in TableB.

Tried something like this:

SELECT     TableAId, Name
FROM       TableA
WHERE      
(
TableAId NOT IN 
                (
                 SELECT TableAId FROM TableB WHERE TimeEnd is null
                )
);

It works fine, when i have some records that have null TimeEnd and some with not null TimeEnd, but it doesnt work when i have no records with no TimeEnd set.

How to fix it?

Upvotes: 0

Views: 106

Answers (3)

codingbiz
codingbiz

Reputation: 26386

Try this

Select * from TableA a
Left Join TableB b ON a.TableAId = b.TableAId 
WHERE b.TimeEnd IS NULL

For Not Null

Select * from TableA a
Left Join TableB b ON a.TableAId = b.TableAId 
WHERE b.TimeEnd IS NOT NULL

Upvotes: 1

peter.aryanto
peter.aryanto

Reputation: 522

I think you may need to join the 2 tables first as the following example demonstrates:

SELECT     TableA.TableAId, TableA.Name
FROM       TableA
INNER JOIN TableB
ON TableA.TableAId = TableB.TableAId
WHERE      
(
TableA.TableAId NOT IN 
                (
                 SELECT TableAId FROM TableB WHERE TimeEnd is null
                )
);

Upvotes: 0

Sebastian Meine
Sebastian Meine

Reputation: 11773

Based on your question there are two possible answers:

IF OBJECT_ID('dbo.TableB') IS NOT NULL 
  DROP TABLE dbo.TableB;
IF OBJECT_ID('dbo.TableA') IS NOT NULL 
  DROP TABLE dbo.TableA;

CREATE TABLE dbo.TableA
  (
    TableAId INT PRIMARY KEY,
    Name VARCHAR(100)
  ); 
CREATE TABLE dbo.TableB
  (
    OtherId INT PRIMARY KEY,
    TableAId INT FOREIGN KEY REFERENCES TableA ( TableAId ),
    TimeBegin DATETIME,
    TimeEnd DATETIME
  );


INSERT  INTO dbo.TableA
        ( TableAId, Name )
VALUES  ( 1, 'aaa' ),
        ( 2, 'bbb' ),
        ( 3, 'ccc' ),
        ( 4, 'ddd' );

INSERT  INTO dbo.TableB
        ( OtherId, TableAId, TimeBegin, TimeEnd )
VALUES  ( 1, 1, GETDATE(), NULL ),
        ( 2, 2, GETDATE(), GETDATE() ),
        ( 3, 2, GETDATE(), NULL ),
        ( 4, 3, GETDATE(), GETDATE() );


SELECT  A.*
FROM    dbo.TableA A
WHERE   EXISTS ( SELECT 1
                 FROM   dbo.TableB B
                 WHERE  A.TableAId = B.TableAId
                        AND B.TimeEnd IS NULL );

SELECT  A.*
FROM    dbo.TableA A
WHERE   EXISTS ( SELECT 1
                 FROM   dbo.TableB B
                 WHERE  A.TableAId = B.TableAId
                        AND B.TimeEnd IS NULL )
        AND NOT EXISTS ( SELECT 1
                         FROM   dbo.TableB B
                         WHERE  A.TableAId = B.TableAId
                                AND B.TimeEnd IS NOT NULL );

The first select returns all records from TableA for which AT LEAST ONE matching record in TableB exists with TimeEnd IS NULL.

The second select returns records from TableA for which ALL matching records in TableB fullfil TimeEnd IS NULL.

The second select can be rewritten as

SELECT  A.*
FROM    dbo.TableA A
LEFT JOIN ( SELECT  TableAId,
                    MIN(CASE WHEN TimeEnd IS NULL THEN 1
                             ELSE 0
                        END) AllAreNull
            FROM    dbo.TableB
            GROUP BY TableAId
          ) AS B
        ON A.TableAId = B.TableAId
WHERE   B.AllAreNull = 1;

This is a little less easy to understand but should execute significantly faster in most cases. But you should run your own performance tests to make sure.

If each record in TableA has at most one record in TableB you can go with the first select statement.

Finally, your code does not match the describing text. If you are actually looking for records that have the endtime valued you need to switch out IS NULL with IS NOT NULL and vice versa in all above select statements.

Upvotes: 1

Related Questions