sel record that don't satisfy the join condi

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

SQL Server Development (2000)

sel record that don't satisfy the join condi

Author

Topic

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 13:48:23

Hi,
I'm working on a query where it gives a result set of 308 records based on the following criteria (join condition & filters), however, I have got 309 records as per one of the source table - DCS_Sales. i need to pick that 1 record which is being dropped as it doesn't satisfy the following criteria & load it into error table.

SELECT
*

FROM
DCS_SALES,
AGREEMENT_CROSS_REFERENCE

WHERE
DCS_SALES.BILL_GROUP_GEN=AGREEMENT_CROSS_REFERENCE.BUSINESS_KEY
and
DCS_SALES.ADMIN_SOURCE_CD='OM'
and
AGREEMENT_CROSS_REFERENCE.SOURCE_SYSTEM='OMNI'
and
AGREEMENT_CROSS_REFERENCE.DELETE_FL='N'
and
AGREEMENT_CROSS_REFERENCE.AGREEMENT_TYPE_ID IN (104362, 104411)

Pls. help!!!

Thanks
Bharat

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 14:27:25

You'll want to use a LEFT OUTER JOIN to get the row that doesn't match the JOIN conditions.

Tara

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 14:46:17

Thanks Tara!
But I'm not getting the desired result set.....in fact I used the following query:
select DCS_SALES.row_id,
DCS_SALES.FILE_DT,
DCS_SALES.EFFECTIVE_DT,
DCS_SALES.POLICY_NBR,
DCS_SALES.PARTICIPATION_SPLIT_PCT,
ltrim(rtrim(DCS_SALES.AGENCY_CD)),
ltrim(rtrim(DCS_SALES.AGENT_CD)),
ltrim(rtrim(DCS_SALES.PREMIUM_CLASS_CD)),
DCS_SALES.SALE_SPLIT_AMT,
DCS_SALES.PRODUCT_NM,
DCS_SALES.ENTITY_KEY,
AGREEMENT_CROSS_REFERENCE.AGREEMENT_ID

from AGREEMENT_CROSS_REFERENCE left outer join dcs_sales
on DCS_SALES.BILL_GROUP_GEN=AGREEMENT_CROSS_REFERENCE.BUSINESS_KEY
where
DCS_SALES.ADMIN_SOURCE_CD='OM'
and
AGREEMENT_CROSS_REFERENCE.SOURCE_SYSTEM='OMNI'
and
AGREEMENT_CROSS_REFERENCE.DELETE_FL='N'
and
AGREEMENT_CROSS_REFERENCE.AGREEMENT_TYPE_ID IN (104362, 104411)

and what I got was that each record in DCS_SALES joined with the Agreement_Cross_reference table records & the result count was 308xno. of records in Agreement-Cross_reference table which satisfied the above condition.

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 14:48:34

Oops!!! I errored out pls. find the query:
select
*

from AGREEMENT_CROSS_REFERENCE left outer join dcs_sales
on NOT DCS_SALES.BILL_GROUP_GEN=AGREEMENT_CROSS_REFERENCE.BUSINESS_KEY
where
DCS_SALES.ADMIN_SOURCE_CD='OM'
and
AGREEMENT_CROSS_REFERENCE.SOURCE_SYSTEM='OMNI'
and
AGREEMENT_CROSS_REFERENCE.DELETE_FL='N'
and
AGREEMENT_CROSS_REFERENCE.AGREEMENT_TYPE_ID IN (104362, 104411)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 14:55:40

I don't understand the use of NOT in your query.

If you posted your DDL for both tables, sample data (in the form of INSERT INTO statements) and the expected result set using your sample data, then we can help you out on your query. Sample data does not need to reflect real data just data to represent your problem.

Tara

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 15:16:10

Here is the ddl for the tables:
CREATE TABLE DB2DBA.DCS_SALES
(
POPULATION_INFO_ID INTEGER NOT NULL,
FILE_DT DATE,
COMPANY_CD CHARACTER(3),
PROCESS_DT DATE,
EFFECTIVE_DT DATE,
POLICY_NBR VARCHAR(15),
PARTICIPATION_SPLIT_PCT DECIMAL(6,5),
AGENCY_CD CHARACTER(3),
AGENT_CD CHARACTER(3),
PLAN_CD CHARACTER(6),
SOURCE_SYSTEM_CD CHARACTER(4),
BILLING_GROUP_NBR CHARACTER(6),
PREMIUM_CLASS_CD CHARACTER(1),
LINE_OF_BUSINESS_CD CHARACTER(3),
SALE_SPLIT_AMT DECIMAL(15,2),
PRODUCT_CD VARCHAR(10),
PRODUCT_NM VARCHAR(25),
ENTITY_KEY INTEGER,
ADMIN_SOURCE_CD CHARACTER(4),
ROW_ID INTEGER GENERATED BY DEFAULT AS IDENTITY( START WITH 1, INCREMENT BY 1, CACHE 20 ),
CREATION_TS TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
LAST_MODIFIED_TS TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CODE_NBR_GEN VARCHAR(18) NOT NULL,
BILL_GROUP_GEN VARCHAR(20)
)

Don't have the DDL for Agreement_Cross_reference as it's a nickname from some other database & I'm not sure of what database is it.....

I'm not sure ghow I can send u the same data. But what I can tell u is the logic of using NOT in my sql query. Basically without NOT I'm getting 308 records while there is one record that is not being picked up coz it doesn't satisfy the join condition. I thot of using NOT on the left outer join to get that one record but it didn't fruitify. Pls. also find below the details about the record as per the DDL given above which doesn't fullfil the necessary criteria & should be loaded in the error table:
141 11/30/2004 001 8/10/2004 8/5/2004 223593131Z998CB 1.00000 224 183 934F00 DCS1 666998 7 T03 16156.00 2OUCP ING ROLLOVER ADVANTAGE IR 230621 OM 65797 12/06/2004 09:46:12 AM 12/06/2004 09:46:12 AM 001~223593131Z998 666998~223593131

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 15:21:35

I can't really help you with an exact solution since you aren't able to provide everything requested. But what I can tell you is that you want a LEFT OUTER JOIN. Then in the WHERE clause, the one record will have a NULL in the right table. If you want it from the left table, then you can use a RIGHT OUTER JOIN or change the order of the tables. Check out this example for more information:



USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

Here is the result set:

au_fname             au_lname                       pub_name          
-------------------- ------------------------------ ----------------- 
Reginald             Blotchet-Halls                 NULL
Michel               DeFrance                       NULL
Innes                del Castillo                   NULL
Ann                  Dull                           NULL
Marjorie             Green                          NULL
Morningstar          Greene                         NULL
Burt                 Gringlesby                     NULL
Sheryl               Hunter                         NULL
Livia                Karsen                         NULL
Charlene             Locksley                       NULL
Stearns              MacFeather                     NULL
Heather              McBadden                       NULL
Michael              O'Leary                        NULL
Sylvia               Panteley                       NULL
Albert               Ringer                         NULL
Anne                 Ringer                         NULL
Meander              Smith                          NULL
Dean                 Straight                       NULL
Dirk                 Stringer                       NULL
Johnson              White                          NULL
Akiko                Yokomoto                       NULL
Abraham              Bennet                         Algodata Infosystems
Cheryl               Carson                         Algodata Infosystems

The LEFT OUTER JOIN includes all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results there is no matching data for most of the authors listed; therefore, these rows contain null values in the pub_name column

That was taken directly from SQL Server Books Online.

Notice the NULLs. The NULLs are when there isn't a match in the publishers table (which is the right table here).

Tara

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 15:37:35

Thanks Tara, however, just assue from ur previous example that:
Authors ---> DCS_Sales table with 309 records
Agreement ---> Publisher table with 99,000 records.
when joined on the following condition:
select *

from dcs_sales left outer join AGREEMENT_CROSS_REFERENCE
on DCS_SALES.BILL_GROUP_GEN=AGREEMENT_CROSS_REFERENCE.BUSINESS_KEY
where
DCS_SALES.ADMIN_SOURCE_CD='OM'
and
AGREEMENT_CROSS_REFERENCE.SOURCE_SYSTEM='OMNI'
and
AGREEMENT_CROSS_REFERENCE.DELETE_FL='N'
and
AGREEMENT_CROSS_REFERENCE.AGREEMENT_TYPE_ID IN (104362, 104411)

gives 308 records, however there is one record that is left out/ dropped as it doesn't satisfy the above mentioned criteria.

What I want is to select that only one record which has been left out/ dropped.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 15:39:53

So how does it not satify the above mentioned criteria? What makes it different? We really need to see an example in order to help.

Tara

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 16:00:17

The result set which it gives 308 records, I'm just giving a sample data (4 records only):
65765 11/30/2004 9/27/2004 071562734Z998CB 1.00000 224 167 7 3825.00 ING ROLLOVER ADVANTAGE IR 246560
5507064

65774 11/30/2004 3/7/2003 086569040Z998CB 0.80000 224 167 7 15719.39 ING ROLLOVER ADVANTAGE IR 246560 5507068

65773 11/30/2004 3/7/2003 086569040Z998CB 0.80000 224 167 7 -15719.39 ING ROLLOVER ADVANTAGE IR 246560 5507068

65575 11/30/2004 3/7/2003 086569040Z998CB 0.20000 057 191 7 3929.85 ING ROLLOVER ADVANTAGE IR 257327 5507068

what I want is only one record which shd look like this:
65797 8/10/2004 8/5/2004 223593131Z998CB 1.00000 224 183 7 16156.00 ING ROLLOVER ADVANTAGE IR 230621 [NULL]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 16:08:50

So how is that record different than the others? We can't see your system, so you really have to spell things out for us. It's hard to imagine what you are seeing. What makes it not satify the criteria?

Tara

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 16:13:28

If u see that record has the last column as NULL which is basically AGREEMENT_CROSS_REFERENCE.AGREEMENT_ID. This is because it doesn't satisfy the join condition. This is the only & only record which I wish to pick & none else. Also, fyi, i'm giving the names of the column that correspond to the ones in the example:
DCS_SALES.row_id,
DCS_SALES.FILE_DT,
DCS_SALES.EFFECTIVE_DT,
DCS_SALES.POLICY_NBR,
DCS_SALES.PARTICIPATION_SPLIT_PCT,
ltrim(rtrim(DCS_SALES.AGENCY_CD)),
ltrim(rtrim(DCS_SALES.AGENT_CD)),
ltrim(rtrim(DCS_SALES.PREMIUM_CLASS_CD)),
DCS_SALES.SALE_SPLIT_AMT,
DCS_SALES.PRODUCT_NM,
DCS_SALES.ENTITY_KEY,
AGREEMENT_CROSS_REFERENCE.AGREEMENT_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-04 : 16:17:15

So you'd do something like this:

SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a
LEFT OUTER JOIN publishers p ON a.city = p.city
WHERE p.pub_name IS NULL

This is using the pubs database. So if you look up above where I gave the example of a LEFT OUTER JOIN, it shows all records, the matches and the unmatches. To get the unmatches, you do the WHERE IS NULL condition like I have in this post.

Tara

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-04 : 16:18:36

Select <whatever you want>
from DCS_Sales
where DCS_Sales.PrimaryKey not in (
select DCS_Sales.PrimaryKey
from <your big join condition>

HTH

=================================================================

Where it is a duty to worship the sun, it is pretty sure to be a crime to examine the laws of heat. -John Morley, statesman and writer (1838-1923)

bharatsql
Starting Member

22 Posts

Posted - 2005-01-04 : 16:37:17

Thanks Tara for ur help!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources