SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Returning Missing Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  12:59:33  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 09/26/2007 :  13:26:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

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

Edited by - SwePeso on 09/26/2007 13:28:18
Go to Top of Page

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  14:12:52  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  14:16:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try before dismissing you must.
Understand the query given to you, you will.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  14:20:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  14:35:00  Show Profile  Reply with Quote
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.
Go to Top of Page

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  14:40:04  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  14:43:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  14:46:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  15:02:12  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  15:08:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/26/2007 15:10:55
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  15:10:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

JAdauto
Posting Yak Master

USA
135 Posts

Posted - 09/26/2007 :  15:42:25  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 09/26/2007 :  15:52:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000