Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

find out which record_ids match on 2 fields and not match a third field. Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 1:28 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 1,876, Visits: 1,663
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
Posted Wednesday, February 13, 2008 1:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:31 PM
Points: 99, Visits: 639
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
Posted Wednesday, February 13, 2008 2:35 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 1,876, Visits: 1,663
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
Posted Wednesday, February 13, 2008 3:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 1:31 PM
Points: 99, Visits: 639
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
Posted Wednesday, February 13, 2008 4:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 1,876, Visits: 1,663
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 »

Add to briefcase ««12

Permissions Expand / Collapse