InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:21:45 GMT20http://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxChanged query to use #input2When 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 queryselect 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_flagFrom #input2 A join #input2 B On A.keyitem = b.keyitem Order by A.keyitemWed, 13 Feb 2008 16:20:47 GMTben.dhttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxBased 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.Wed, 13 Feb 2008 15:15:53 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxJohn, 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 #Input1values ( '1', '113456')insert into #Input1values ( '2','113457')insert into #Input1values ( '3','113458')insert into #Input1values ( '4','113459')insert into #Input1values ( '5','113460')insert into #Input1values ( '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 #Input2values ('1', '123','desc','blue','rg','113456')insert into #Input2values ( '2','123','desc','blue','rg','113457')insert into #Input2values ( '3','123','desc','blue','rg','113458')insert into #Input2values ( '4','123','desc2','blue','rg','113459')insert into #Input2values ('5', '123','desc','blue','rg','113456')insert into #Input2values ( '6','123','desc','blue','rg','113456')insert into #Input2values ( '7','123','desc','blue','rg','113457')insert into #Input2values ( '8','123','desc','blue','rg','113457')insert into #Input2values ( '7','123','desc','blue','rg','113458')insert into #Input2values ( '8','123','desc','blue','rg',',113458')insert into #Input2values ( '9','123','desc','blue2','rg','113459')insert into #Input2values ( '10','123','desc','blue3','rg','113459')insert into #Input2values ( '11','123','desc','blue3','rg','113459')insert into #Input2values ( '12','123','desc','blue3','rg','113460')Select Record_Id, #input2.keyItem, item, i_desc, S_desc, M_desc, ' ' as statusfrom #input1 Join #input2on #input1.KeyItem = #input2.KeyItemorder by record_Id drop table #input1 drop table #input2Wed, 13 Feb 2008 14:35:15 GMTben.dhttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxWhich columns in the temp table are in which master table?I am interested in the solution w/o using the temp table, if possible.Wed, 13 Feb 2008 13:33:34 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxThe 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 Wed, 13 Feb 2008 13:28:44 GMTben.dhttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxAgreed - no reason it couldn't be run against the master, but since OP gave us a temp table, that's what I used.Wed, 13 Feb 2008 11:56:14 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxI just assumed the temp table represented the master table for example and testing purposes.Wed, 13 Feb 2008 11:24:21 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxI know this isn't the question that was asked, but...Is there a reason this couldn't be run from the two master tables, skipping the #Temp table?Edit: This question was really for the OP, not all the magnanimous responders that were helping him out. ;)Wed, 13 Feb 2008 11:17:14 GMTJohn Beggshttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxMatt's solution is more efficient than mine. I thought about using a CTE, but have not used them enough to be comfortable. If you set statustics IO on my solution returns: Scan count 2, logical reads 15 physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.And Matt's: Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Wed, 13 Feb 2008 09:59:01 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxDo you care which of the three doesn't match? If not - you could use JUST the "matches" part below[code];with JoinMath as ( Select a.recordid, a.tempcheckid, case when A.I_desc = B.I_desc then 1 else 0 end + case when A.m_desc = B.m_desc then 1 else 0 end + case when A.item = B.item then 1 else 0 end as Matches, --create a pseudo bitbucket case when A.I_desc = B.I_desc then 1 else 0 end + case when A.m_desc = B.m_desc then 2 else 0 end + case when A.item = B.item then 1 else 0 end as MatchFilter From #tempcheck a inner join #tempcheck b on a.recordID=b.recordID and a.tempcheckID<>a.tempcheckid )Select *, case when MatchFilter & 1 = 0 then 'Type1' when MatchFilter & 2 = 0 then 'Type4' when MatchFilter & 4 = 0 then 'Type3' ELSE 'Type2' end as MatchType from JoinMath where Matches>1 [/code]Wed, 13 Feb 2008 07:56:36 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxI won't say that this is the best way to do it, but I believe a simple self join on record_id with a case statement will work:[code]select A.*, B.*, 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_flagFrom #tempcheck A join #tempcheck B On A.record_id = B.record_id And A.tempcheck_id > B.tempcheck_idOrder by A.tempcheck_id[/code]Wed, 13 Feb 2008 06:19:49 GMTunclebiguns-494774http://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxEnd result is a report show the type for each row. tempcheck_id RECORD_ID Item I_desc S_desc M_desc Status_Flag1 1 123 desc blue rg Type12 1 124 desc blue rg Type13 1 125 desc blue rg Type14 2 123 desc blue rg Type25 2 123 desc blue rg Type26 2 123 desc blue rg Type27 3 123 desc blue rg Type28 3 123 desc blue rg Type29 3 123 desc blue rg Type210 3 123 desc blue rg Type211 4 123 desc2 blue rg Type312 4 123 desc2 blue rg Type313 4 123 desc3 blue rg Type314 4 123 desc3 blue rg Type3Where Type1 I_desc and M_desc match and Item is not, for same for record_Id.Where Type2 I_desc and M_Desc and Item all match for same record_ID.Where Type3 I_desc is not matched and M_Desc and Item are matched, for same record_id.Tue, 12 Feb 2008 15:16:09 GMTben.dhttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxCan you post what you want the final result to look like?Tue, 12 Feb 2008 13:45:51 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxTry something like this:[code]select distinct tempcheck_id, record_idfrom #tempcheckinner join (select tempcheck_id, min(record_id) as min_id from #tempcheck group by tempcheck_id) Sub on main.tempcheck_id = sub.tempcheck_id and main.record_id = sub.min_idexceptselect t1.tempcheck_id, t1.record_idfrom #tempcheck t1inner join #tempcheck t2 on t1.record_id = t2.record_id and t1.tempcheck_id < t2.tempcheck_id and t1.item = t2.item and t1.i_desc = t2.i_desc and t1.s_desc = t2.s_desc and t1.m_desc = t2.m_descinner join #tempcheck t3 on t1.record_id = t3.record_id and t2.tempcheck_id < t3.tempcheck_id and t1.item = t3.item and t1.i_desc = t3.i_desc and t1.s_desc = t3.s_desc and t1.m_desc = t3.m_desc[/code]You may need to play around with the join criteria to get exactly what you want, but it should put you in the right direction.I noticed you have four of some record IDs. The above code will find if any three of them match, ignoring and unmatched fourth record. If you want to include unmatched fourth records, create a second query that extends the joins by one.Tue, 12 Feb 2008 13:41:25 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic454661-338-1.aspxI need to match up information from 2 different sources.one table has a record_ID used to track results.It is inner joined to second source, creating the data below.I can classify the data as matched, unmatched and multiple matches.Now I need to classify the multiple matches further.How do if find out which record_ids match on 2 fields and not match a third field. With any other row with same record_id and update status for that row.The first three rows meet the criteria where S_desc and M_desc match , but not ItemThe next three rows fails because all three row match on Item, S_desc and M_desc.Then update the status in a status table with one to one mappingCREATE TABLE [dbo].[#tempCheck]( [tempcheck_id] [numeric](10,0) null, [RECORD_ID] [numeric](10, 0) NULL, [Item] varchar(15) NULL, [I_desc] varchar(24) null, [S_desc] varchar(50) null, [M_desc] varchar(10) null )insert into #tempcheckvalues ('1', '1','123','desc','blue','rg')insert into #tempcheckvalues ( '2','1','124','desc','blue','rg')insert into #tempcheckvalues ( '3','1','125','desc','blue','rg')insert into #tempcheckvalues ( '4','2','123','desc','blue','rg')insert into #tempcheckvalues ( '5','2','123','desc','blue','rg')insert into #tempcheckvalues ( '6','2','123','desc','blue','rg')insert into #tempcheckvalues ( '7','3','123','desc','blue','rg')insert into #tempcheckvalues ( '8','3','123','desc','blue','rg')insert into #tempcheckvalues ( '9','3','123','desc','blue','rg')insert into #tempcheckvalues ( '10','3','123','desc','blue','rg')insert into #tempcheckvalues ( '11','4','123','desc2','blue','rg')insert into #tempcheckvalues ( '12','4','123','desc2','blue','rg')insert into #tempcheckvalues ( '13','4','123','desc3','blue','rg')insert into #tempcheckvalues ( '14','4','123','desc3','blue','rg')CREATE TABLE [dbo].[#tempflag]( [tempcheck_id] [numeric](10,0) null, [RECORD_ID] [numeric](10, 0) NULL, [Status_Flag] varchar(15) NULL)insert into #tempflag (tempcheck_id, RECORD_ID, Status_Flag)select tempcheck_id, RECORD_ID, 'Not_Matched' as Status_FlagFrom #tempcheckselect tempcheck_id, RECORD_ID, Status_FlagFrom #tempflagdrop table #tempcheckdrop table #tempflagThanks for your assistance.BenTue, 12 Feb 2008 13:07:26 GMTben.d