find out which record_ids match on 2 fields and not match a third field.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » find out which record_ids match on 2 fields...

15 posts, Page 2 of 2

««1 2

find out which record_ids match on 2 fields and not match a third field.

Rate Topic

Display Mode

Topic Options

Author

Message

ben.d

Posted Wednesday, February 13, 2008 1:28 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,517, Visits: 1,383

The Temp table is for example and does represent a table created from joining 2 other tables.

When the 2 other tables are joined, I get multiple rows for some of the record_Ids.

For Each one of the multiple rows, I need to assign a status.
status is determined by how many field match across the multiple rows for same record_id.

Thanks,
Ben

Post #455348

John Beggs

Posted Wednesday, February 13, 2008 1:33 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 28, 2013 9:39 AM
Points: 99, Visits: 613

Which columns in the temp table are in which master table?

I am interested in the solution w/o using the temp table, if possible.

Post #455356

ben.d

Posted Wednesday, February 13, 2008 2:35 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,517, Visits: 1,383

John,
Here is simplification of the 2 tables.
They are matched on one key item.

#input1 - row 5 has only one match.
#input1 - row 6 has no matches.

++++++
CREATE TABLE [dbo].[#Input1](
[RECORD_ID] [numeric](10, 0) not NULL,
[keyItem] varchar(15) not null )

insert into #Input1
values ( '1', '113456')
insert into #Input1
values ( '2','113457')
insert into #Input1
values ( '3','113458')
insert into #Input1
values ( '4','113459')
insert into #Input1
values ( '5','113460')
insert into #Input1
values ( '6','113461')

CREATE TABLE [dbo].[#Input2](
[input2_ID] [numeric](10, 0) not NULL,
[Item] varchar(15) NULL,
[I_desc] varchar(24) null,
[S_desc] varchar(50) null,
[M_desc] varchar(10) null,
[KeyItem] varchar(15) not null )

insert into #Input2
values ('1', '123','desc','blue','rg','113456')
insert into #Input2
values ( '2','123','desc','blue','rg','113457')
insert into #Input2
values ( '3','123','desc','blue','rg','113458')
insert into #Input2
values ( '4','123','desc2','blue','rg','113459')
insert into #Input2
values ('5', '123','desc','blue','rg','113456')
insert into #Input2
values ( '6','123','desc','blue','rg','113456')
insert into #Input2
values ( '7','123','desc','blue','rg','113457')
insert into #Input2
values ( '8','123','desc','blue','rg','113457')
insert into #Input2
values ( '7','123','desc','blue','rg','113458')
insert into #Input2
values ( '8','123','desc','blue','rg',',113458')
insert into #Input2
values ( '9','123','desc','blue2','rg','113459')
insert into #Input2
values ( '10','123','desc','blue3','rg','113459')
insert into #Input2
values ( '11','123','desc','blue3','rg','113459')
insert into #Input2
values ( '12','123','desc','blue3','rg','113460')

Select Record_Id, #input2.keyItem, item, i_desc, S_desc, M_desc, ' ' as status
from #input1 Join #input2
on #input1.KeyItem = #input2.KeyItem
order by record_Id

drop table #input1
drop table #input2

Post #455411

John Beggs

Posted Wednesday, February 13, 2008 3:15 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 28, 2013 9:39 AM
Points: 99, Visits: 613

Based on the master table structure you posted, there is nothing of use in the "parent" table, Input1.

In that case, the methods presented by the other posters can be applied directly to the "child" table, i.e. Input2. If you did need to use the parent table, for example to filter on some field that you haven't mentioned here, you could just add it into the joins of the other queries already presented.

Post #455430

ben.d

Posted Wednesday, February 13, 2008 4:20 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,517, Visits: 1,383

Changed query to use #input2

When I run the query below, I get 44 rows.
but When I join the 2 tables I only get 14 rows.

Expecting only have 14 rows in the query

select A.*,
Case
When A.item = B.item and A.I_desc = B.I_desc and A.m_desc = B.m_desc then 'Type 2'
When A.I_desc = B.I_desc and A.m_desc = B.m_desc then 'Type 1'
When A.item = B.item and A.m_desc = B.m_desc then 'Type 3'
Else 'No Match' End as status_flag
From #input2 A join #input2 B
On A.keyitem = b.keyitem
Order by A.keyitem

Post #455450

« Prev Topic | Next Topic »

15 posts, Page 2 of 2

««1 2

Permissions