Surpluss 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)

Surpluss records

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-26 : 12:39:34

Hello,

I have three tables: e_i_a, e_i_a_t and s_s_i. All three can be joined on uniq_id.

When I do inner join on the first two:

select count(*) from e_i_a eia
inner join e_i_a_t eiat on eia.UNIQ_ID = eiat.UNIQ_ID

I'm getting 94989 records

I need to resolve one of the fields from the third table. It has multiple records per ID, but the field I need is the same across these records. So I thought I need to include DISTINCT subquery. But this:

select count(*) from e_i_a eia
inner join e_i_a_t eiat on eia.UNIQ_ID = eiat.UNIQ_ID
left join (select distinct uniq_id, other_field from s_s_i) as ssi on eia.uniq_id = ssi.uniq_id

returns 94999 records.

Where do I look for these 10 records, and why could they be there? Am I making some fundamental mistake here?
Could it be that some records in ssi have more than one other_field value for the same ID? How would I fish these out?

TIA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 14:30:50

Mistake? Yes. LEFT JOIN a table does not make a difference when counting only original table.

SELECT SUM(1) AS Records_in_EIA,
SUM(CASE when e_i_a_t is null then 0 else 1 end) AS Records_in_EIAT
FROM e_i_a
LEFT JOIN e_i_a_t on e_i_a_t.uniq_id = e_i_a.uniq_id

Peter Larsson
Helsingborg, Sweden

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-26 : 14:58:34

I think we're talking about two different things.

declare @eia table (uniq_id int, cde char(1))
insert	@eia
select	1, 'A' union all
select	2, 'B' union all
select	3, 'C' union all
select  4, 'D' union all
select  5, 'E'

declare @eiat table (uniq_id int, frm varchar(6))
insert	@eiat
select	1, 'WIDE' union all
select	2, 'WIDE' union all
select	2, 'FRT'  union all
select	3, 'CVS'  union all
select	4, 'WIDE' union all
select	4, 'MRK'  union all
select  5, 'FRT'

declare @ssi table (uniq_id int, other_field varchar(6))
insert	@ssi
select	1, '201'  union all
select	2, '94'   union all
select	2, '94'   union all
select	3, '3021' union all
select	4, '2'    union all
select  5, '0'


/*
I need everything from inner join of the first two tables.  This gives me table with one or more records for each id:*/

select eia.uniq_id
, eia.cde
, eiat.frm
from @eia eia
inner join @eiat eiat on eia.UNIQ_ID = eiat.UNIQ_ID

select count(*)
from @eia eia
inner join @eiat eiat on eia.UNIQ_ID = eiat.UNIQ_ID

/* I'm getting 7, which is what I'm expecting 

But I also need to add other_field to the result:
*/
select eia.uniq_id
, eia.cde
, eiat.frm
, ssi.other_field
from @eia eia
inner join @eiat eiat on eia.UNIQ_ID = eiat.UNIQ_ID
left join (select distinct uniq_id, other_field from @ssi) as ssi on eia.uniq_id = ssi.uniq_id

And for the data above I'm getting 7 records as well. But in my real life data, there is discrepancy of 10 records. My question is - is it because there is/are record(s) in ssi table, which has more than one value in other_field? And if so - how do I list offending uniq_id?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 01:11:40

To get the 10 records,

select t1.* from e_i_a as t1
left join e_i_a_t as t2 on t2.uniq_id = t1.uniq_id
where t2.uniq_id is null

Peter Larsson
Helsingborg, Sweden

rcurrey
Starting Member

30 Posts

Posted - 2007-02-27 : 11:03:31

To find the uniq_id which has multiple values in ssi:

select eia.UNIQ_ID, count(*) from e_i_a eia
inner join e_i_a_t eiat on eia.UNIQ_ID = eiat.UNIQ_ID
left join (select distinct uniq_id, other_field from s_s_i) as ssi on eia.uniq_id = ssi.uniq_id
group by eia.uniq_id
having count(*) > 1

Thanks,
Rich

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-27 : 13:36:34

Thanks to both of you, and I apologize for not expressing myself clearly.

The culprit in my case is the ssi table. It's supposed to have multiple rows with the same ID, each with the same value of other_field. But there are records that break this rule, as in:

declare @ssi table (uniq_id int, other_field varchar(6))
insert	@ssi
select	1, '201'  union all
select	2, '94'   union all  -- record with uniq_id=2 is fine: '94'
select	2, '94'   union all  -- ...and '94'
select	3, '3021' union all
select	4, '7'    union all  -- ...but this one isn't: '7' here
select	4, '8'    union all  -- ...and '8' here
select  5, '0'

So - how do I fish out IDs, but not just for duplicates (duplicates in this table are expected), but duplicates that have different value of other_field?

Sorry for the confusion, thanks for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-27 : 14:20:45

[code]-- prepare sample data
declare @ssi table (uniq_id int, other_field varchar(6))

insert @ssi
select 1, '201' union all
select 2, '94' union all -- record with uniq_id=2 is fine: '94'
select 2, '94' union all -- ...and '94'
select 3, '3021' union all
select 4, '7' union all -- ...but this one isn't: '7' here
select 4, '8' union all -- ...and '8' here
select 5, '0'

-- show the expected result?
SELECT uniq_id
FROM @ssi
GROUP BY uniq_id
HAVING MIN(other_field) <> MAX(other_field)[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources