Returning Missing Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Returning Missing Records

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 12:59:33

I am having a heck of a time writing SQL queries to return data that is invalid. I am helping to write a verification tool and I can get data that is incorrect or doesn't add up. What I am having trouble getting is data that is missing that should be there. For one of the easier examples, I have the following tables: Entity, EntityBalance, BalanceType. Balance type can be any number of records, usually 1-3, but can be any. Entity can contain all my people. For every record in Entity, I need that many records * the number of records in BAlanceType in the EntityBalance. So, if I have 2 BalanceTypes and 2 Entity records, then I should have 4 records in EntityBalance. I have an example below. But my problem is if I only have 3 records. I cannot get the Entity that is missing one of the BalanceTypes to appear in my result set. Only if he is missing both does he show up. If I use the test data below, I would like the results to return Jane Doe to be missing the 'Sub' Balance record.

CREATE TABLE [dbo].[EntityBalance] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[EntityID] [int] NOT NULL ,
[BalanceTypeID] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[BalanceType] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]

CREATE TABLE [dbo].[Entity] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]

INSERT BalanceType Values (1, 'Main')
INSERT BalanceType Values (2, 'Sub')

INSERT EntityBalance Values (1, 1000, 1)
INSERT EntityBalance Values (1, 1000, 2)
INSERT EntityBalance VAlues (1, 1001, 1)

INSERT Entity Values (1000, 'John', 'Smith')
INSERT Entity Values (1000, 'Jane', 'Doe')

(I hope all that works)

Thanks,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 13:26:42

[code]SELECT DISTINCT e.ID AS EntityID,
bt.BalanceTypeID
FROM Entity AS e
CROSS JOIN BalanceType AS bt
LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.ID AND eb.BalanceTypeID = bt.BalanceTypeID
WHERE eb.EntityID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 14:12:52

quote:

WHERE eb.EntityID IS NULL

It is not really where eb.EntityID is Null because there will be no record. That would fund a record where that field is blank, right? But it is not that I would have a blank field.. it is that entityid 1001 is completely missing a record for BalanceTypeID 2 (sub) Does that make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:16:40

Try before dismissing you must.
Understand the query given to you, you will.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:20:31

[code]SELECT DISTINCT e.ID AS EntityID,
bt.ID AS BalanceTypeID
FROM Entity AS e
CROSS JOIN BalanceType AS bt
LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.ID AND eb.BalanceTypeID = bt.BalanceTypeID
WHERE eb.EntityID IS NULL[/code]The red part return all possible combinations possible of entities and balancetypes.
The green part return the current entitybalances.
The blue part removes all current entitybalances from all possible combinations.

E 12°55'05.25"
N 56°04'39.16"

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 14:35:00

quote:

Try before dismissing you must.
Understand the query given to you, you will.

I did try it with my live data and with the sample tables and data that I made up thinking I was missing something. I am getting no results. I am wanting to get Jane Doe in the result set because she is missing an EntityBalance record for BalanceType 2. I appreciate your help on this, but I assure you that I did test it.

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 14:40:04

quote:
Originally posted by Peso

SELECT DISTINCT e.ID AS EntityID,
bt.ID AS BalanceTypeID
FROM Entity AS e
CROSS JOIN BalanceType AS bt
LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.ID AND eb.BalanceTypeID = bt.BalanceTypeID
WHERE eb.EntityID IS NULL
The red part return all possible combinations possible of entities and balancetypes.
The green part return the current entitybalances.
The blue part removes all current entitybalances from all possible combinations.

E 12°55'05.25"
N 56°04'39.16"

When I only execute the Red portion, I only get John Smith and his two Balance Types. I do not even show Jane Doe on the resultset.

When I execute the Red and Green together, I get the same results. (John Smith listed twice)

When I add the blue portion in, I get nada.

I can see what you are saying, but it is not happening. I have struggled with this for 3 days. I cannot believe I am having so much trouble getting a missing record out of data. Thanks,
JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:43:52

It is not clear if you want Entity.ID or Entity.Number.
Please repost proper and accurate sample data!
I can't even run the sample code you posted originally.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 14:46:30

[code]SELECT DISTINCT e.Number,
bt.ID AS BalanceTypeID
FROM Entity AS e
CROSS JOIN BalanceType AS bt
LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.Number AND eb.BalanceTypeID = bt.ID
WHERE eb.EntityID IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 15:02:12

Here is what I have done to create the data. Forgive me for the typos. I hope to not have offended you as I am grateful for your help and I feel as though you are losing patience with me. I am just frustrated at a loss and as with every other project, I have a month end deadline and many tables to get missing data such as this out to the user. Thanks again.

CREATE TABLE [dbo].[EntityBalance] (
[ID] [int] NOT NULL ,
[EntityID] [int] NOT NULL ,
[BalanceTypeID] [int] NOT NULL) ON [PRIMARY]
Go

INSERT EntityBalance Values (1, 1000, 1)
INSERT EntityBalance Values (1, 1000, 2)
INSERT EntityBalance VAlues (1, 1001, 1)

CREATE TABLE [dbo].[BalanceType] (
[ID] [int] NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

INSERT BalanceType Values (1, 'Main')
INSERT BalanceType Values (2, 'Sub')

CREATE TABLE [dbo].[Entity] (
[ID] [int] NOT NULL ,
[Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO

INSERT Entity Values (1,1000, 'John', 'Smith')
INSERT Entity Values (2,1000, 'Jane', 'Doe')

This is what I am using to test the results which is just an enhanced version of your query to return descriptive data:

SELECT DISTINCT e.Number, e.LastName,
bt.ID AS BalanceTypeID, bt.Description
FROM Entity AS e
CROSS JOIN BalanceType AS bt
LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.Number AND eb.BalanceTypeID = bt.ID
WHERE eb.EntityID IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:08:16

With the test data above, change Jane Doe to 1001 from 1000.

Use this update query to do the modification

update entity
set number = 1001
where id = 2

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:10:00

If you do that, you will get the resultset

Number	LastName	BalanceTypeID	Description
1001	Doe		2		Sub

with this query

SELECT DISTINCT	e.Number,
		e.LastName,
		bt.ID AS BalanceTypeID,
		bt.Description
FROM		Entity AS e
CROSS JOIN	BalanceType AS bt
LEFT JOIN	EntityBalance AS eb ON eb.EntityID = e.Number AND eb.BalanceTypeID = bt.ID
WHERE		eb.EntityID IS NULL
ORDER BY	e.Number,
		bt.ID

E 12°55'05.25"
N 56°04'39.16"

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-09-26 : 15:42:25

LEFT JOIN EntityBalance AS eb ON eb.EntityID = e.Number

Yeah! I got Jane Doe. (AGain.. sorry for the typeo with the 1000 vs 1001). This may seem like a dumb question so please bare with me. I need to make sure I understand it so that I can apply it to all these other items I have to do the same for. In the previous example, you joined eb.EntityID = e.ID and I got no results. In this one you joined eb.EntityID = e.Number, which returned the result set I needed, but I dont understand it since they are not really equal fields. ???? I am assuming it has do with the Left join, but I guess I need it in idiot terms. Thanks for helping me through this.

JAdauto

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 15:52:30

It was not clear with first sample data which column the insert value 1000 went to. That's all.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources