Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  Old Forums  CLOSED - General SQL Server  Algorithm Forum Locked  Printer Friendly
Author  Topic
Page: of 4

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  03:09:52 Hello,anyone has the clue how to solve this situation. I have a table with description of some connections...CON1,CON2,CON3...CON8 (I will assume I have CON1-CON3 in example)1,2,32,1,33,2,11,5,66,6,4If the user search for connection 3,2,1 I should return following rows1,2,32,1,33,2,13,2,1 is the same with combination of all 3Anyone has an idea?I thought to use some sum row, but it won'r help because1,2,3 (6) would be the same as 4,1,1 (6)... which is not OK. Edited by - zurbum on 07/05/2006 03:11:12

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  03:24:25 Take a look at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66605Peter LarssonHelsingborg, Sweden

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  03:39:15 Thank you for help.But I have found an easy but effective solutionSELECT COL1,COL2,COL3 FROM(SELECT COL1, COL2,COL3, COL1+COL2+COL3 AS COL_SUM, COL1*COL2*COL3 AS COL_MUL FROM TABLE_X) AS TEMPWHERE TEMP.COL_SUM=INPUT1+INPUT2+INPUT3 AND TEMP.COL_MUL=INPUT1*INPUT2*INPUT3When user search for connection 1,2,3 the query ruturns all rows with this combination.Anyone has better solution?I forgot to mention that connection can not be 0. Edited by - zurbum on 07/05/2006 03:45:55

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  04:08:36 Are there always three search numbers? What if you search for 1 and 6?Peter LarssonHelsingborg, Sweden

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  04:32:44 Yees, there must be always 3 search numbers. Also the numbers are >0.

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  04:36:14 quote:Originally posted by zurbumYees, there must be always 3 search numbers. Also the numbers are >0.Then you can't use your logic. Your logic is based on mathematical term perfect number.These numbers gives same sum and product```3 6 8 4 4 9```Sum is 17, product is 144.Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 04:38:40

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  04:42:42 Here is a god-awful hack, but it works for any case.1. Search terms could be 1 up to 8 numbers.2. Duplicate search terms are allowed and matched correctly. Searching for 6 and 6 only retrieves rows with two 6 numbers.Here is the code```-- prepare test data declare @test table (con1 int, con2 int, con3 int, con4 int, con5 int, con6 int, con7 int, con8 int) insert @test (con1, con2, con3) select 1, 2, 3 union all select 2, 1, 3 union all select 3, 2, 1 union all select 1, 5, 6 union all select 6, 6, 4 -- define search variables declare @con1 int, @con2 int, @con3 int, @con4 int, @con5 int, @con6 int, @con7 int, @con8 int select @con4 = 6, @con7 = 6 declare @search table (id int identity(1, 1), con int) insert @search select @con1 where @con1 is not null union all select @con2 where @con2 is not null union all select @con3 where @con3 is not null union all select @con4 where @con4 is not null union all select @con5 where @con5 is not null union all select @con6 where @con6 is not null union all select @con7 where @con7 is not null union all select @con8 where @con8 is not null -- do the search! select distinct t.* from @test t left join @search s1 on t.con1 = s1.con left join @search s2 on t.con2 = s2.con left join @search s3 on t.con3 = s3.con left join @search s4 on t.con4 = s4.con left join @search s5 on t.con5 = s5.con left join @search s6 on t.con6 = s6.con left join @search s7 on t.con7 = s7.con left join @search s8 on t.con8 = s8.con where case when isnull(s1.id, 0) <> isnull(s2.id, 0) and isnull(s1.id, 0) <> isnull(s3.id, 0) and isnull(s1.id, 0) <> isnull(s4.id, 0) and isnull(s1.id, 0) <> isnull(s5.id, 0) and isnull(s1.id, 0) <> isnull(s6.id, 0) and isnull(s1.id, 0) <> isnull(s7.id, 0) and isnull(s1.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s2.id, 0) <> isnull(s3.id, 0) and isnull(s2.id, 0) <> isnull(s4.id, 0) and isnull(s2.id, 0) <> isnull(s5.id, 0) and isnull(s2.id, 0) <> isnull(s6.id, 0) and isnull(s2.id, 0) <> isnull(s7.id, 0) and isnull(s2.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s3.id, 0) <> isnull(s4.id, 0) and isnull(s3.id, 0) <> isnull(s5.id, 0) and isnull(s3.id, 0) <> isnull(s6.id, 0) and isnull(s3.id, 0) <> isnull(s7.id, 0) and isnull(s3.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s4.id, 0) <> isnull(s5.id, 0) and isnull(s4.id, 0) <> isnull(s6.id, 0) and isnull(s4.id, 0) <> isnull(s7.id, 0) and isnull(s4.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s5.id, 0) <> isnull(s6.id, 0) and isnull(s5.id, 0) <> isnull(s7.id, 0) and isnull(s5.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s6.id, 0) <> isnull(s7.id, 0) and isnull(s6.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s7.id, 0) <> isnull(s8.id, 0) then 1 else 0 end = (SELECT COUNT(*) FROM @Search)```Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 04:46:53

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  04:43:05 :( You're right, I thought that I found a solution to quickly.Do you have some idea?

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  04:47:31 Peter, thanks for help!

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  05:04:44 quote:Originally posted by zurbumPeter, thanks for help!You're welcome.Actually you were not far from an solution. With numbers from 1 to 9, there are only four more triples of number that gives the same sum and product```1 6 6 2 2 9 2 6 6 3 3 8```Good luck!Peter LarssonHelsingborg, Sweden

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  07:29:33 Peter,The simplest solution I see right now is to write a function with 8 input params which returns string of this 8 params sorted. Edited by - zurbum on 07/05/2006 07:35:21

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  07:51:34 You can easily rewrite my algorithm to accept three parameters instead of 8! The parameters does not need to be sorted either.```create function dbo.fnSearchAnyColumn ( @con1 int, @con2 int, @con3 int ) RETURNS TABLE AS BEGIN declare @search table (id int identity(1, 1), con int) insert @search select @con1 where @con1 is not null union all -- This is the only select @con2 where @con2 is not null union all -- code needed to select @con3 where @con3 is not null -- be changed! RETURN ( select distinct mytable.* from mytable t left join @search s1 on t.con1 = s1.con left join @search s2 on t.con2 = s2.con left join @search s3 on t.con3 = s3.con left join @search s4 on t.con4 = s4.con left join @search s5 on t.con5 = s5.con left join @search s6 on t.con6 = s6.con left join @search s7 on t.con7 = s7.con left join @search s8 on t.con8 = s8.con where case when isnull(s1.id, 0) <> isnull(s2.id, 0) and isnull(s1.id, 0) <> isnull(s3.id, 0) and isnull(s1.id, 0) <> isnull(s4.id, 0) and isnull(s1.id, 0) <> isnull(s5.id, 0) and isnull(s1.id, 0) <> isnull(s6.id, 0) and isnull(s1.id, 0) <> isnull(s7.id, 0) and isnull(s1.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s2.id, 0) <> isnull(s3.id, 0) and isnull(s2.id, 0) <> isnull(s4.id, 0) and isnull(s2.id, 0) <> isnull(s5.id, 0) and isnull(s2.id, 0) <> isnull(s6.id, 0) and isnull(s2.id, 0) <> isnull(s7.id, 0) and isnull(s2.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s3.id, 0) <> isnull(s4.id, 0) and isnull(s3.id, 0) <> isnull(s5.id, 0) and isnull(s3.id, 0) <> isnull(s6.id, 0) and isnull(s3.id, 0) <> isnull(s7.id, 0) and isnull(s3.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s4.id, 0) <> isnull(s5.id, 0) and isnull(s4.id, 0) <> isnull(s6.id, 0) and isnull(s4.id, 0) <> isnull(s7.id, 0) and isnull(s4.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s5.id, 0) <> isnull(s6.id, 0) and isnull(s5.id, 0) <> isnull(s7.id, 0) and isnull(s5.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s6.id, 0) <> isnull(s7.id, 0) and isnull(s6.id, 0) <> isnull(s8.id, 0) then 1 else 0 end + case when isnull(s7.id, 0) <> isnull(s8.id, 0) then 1 else 0 end = (SELECT COUNT(*) FROM @Search) ) END```The other 8 formulas/joins there are, are just for searching in all eight original columns, not having 8 search numbers.Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 07:59:15

zurbum
Yak Posting Veteran

Norway
55 Posts

 Posted - 07/05/2006 :  08:06:44 Tack Peter!

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  08:21:06 Varsågod!However, this is the function I would use, in case future requirements should allow more than 3 search values.The only drawback is that duplicate rows are not returned. But hopefully you have some kind of identity column, right?Then just add the identity column to the output of the function, and to the last insert statement.```CREATE FUNCTION dbo.fnSearchAnyColumn ( @con1 int = NULL, @con2 int = NULL, @con3 int = NULL, @con4 int = NULL, @con5 int = NULL, @con6 int = NULL, @con7 int = NULL, @con8 int = NULL ) RETURNS @Cons TABLE ( -- identity column here, con1 int, con2 int, con3 int, con4 int, con5 int, con6 int, con7 int, con8 int ) AS BEGIN DECLARE @Search TABLE ( ID INT IDENTITY(1, 1), con INT ) INSERT @Search SELECT @con1 WHERE @con1 IS NOT NULL UNION ALL SELECT @con2 WHERE @con2 IS NOT NULL UNION ALL SELECT @con3 WHERE @con3 IS NOT NULL UNION ALL SELECT @con4 WHERE @con4 IS NOT NULL UNION ALL SELECT @con5 WHERE @con5 IS NOT NULL UNION ALL SELECT @con6 WHERE @con6 IS NOT NULL UNION ALL SELECT @con7 WHERE @con7 IS NOT NULL UNION ALL SELECT @con8 WHERE @con8 IS NOT NULL INSERT @Cons ( -- identity column here, con1, con2, con3, con4, con5, con6, con7, con8 ) SELECT DISTINCT -- identity column here, t.con1, t.con2, t.con3, t.con4, t.con5, t.con6, t.con7, t.con8 FROM MyTable t LEFT JOIN @search s1 ON t.con1 = s1.con LEFT JOIN @search s2 ON t.con2 = s2.con LEFT JOIN @search s3 ON t.con3 = s3.con LEFT JOIN @search s4 ON t.con4 = s4.con LEFT JOIN @search s5 ON t.con5 = s5.con LEFT JOIN @search s6 ON t.con6 = s6.con LEFT JOIN @search s7 ON t.con7 = s7.con LEFT JOIN @search s8 ON t.con8 = s8.con WHERE CASE WHEN ISNULL(s1.ID, 0) <> ISNULL(s2.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s3.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s4.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s5.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s6.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s1.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s2.ID, 0) <> ISNULL(s3.ID, 0) AND ISNULL(s2.ID, 0) <> ISNULL(s4.ID, 0) AND ISNULL(s2.ID, 0) <> ISNULL(s5.ID, 0) AND ISNULL(s2.ID, 0) <> ISNULL(s6.ID, 0) AND ISNULL(s2.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s2.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s3.ID, 0) <> ISNULL(s4.ID, 0) AND ISNULL(s3.ID, 0) <> ISNULL(s5.ID, 0) AND ISNULL(s3.ID, 0) <> ISNULL(s6.ID, 0) AND ISNULL(s3.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s3.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s4.ID, 0) <> ISNULL(s5.ID, 0) AND ISNULL(s4.ID, 0) <> ISNULL(s6.ID, 0) AND ISNULL(s4.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s4.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s5.ID, 0) <> ISNULL(s6.ID, 0) AND ISNULL(s5.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s5.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s6.ID, 0) <> ISNULL(s7.ID, 0) AND ISNULL(s6.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END + CASE WHEN ISNULL(s7.ID, 0) <> ISNULL(s8.ID, 0) THEN 1 ELSE 0 END = (SELECT COUNT(*) FROM @Search) RETURN END```This way, if you don't use con4-con8, they will default to NULL and as such, they make no difference.And for the time being, I would make the restriction to three search values in end-user application.Call with any three of the parameters set to a number and the rest to NULL or DEFAULT`SELECT * FROM dbo.fnSearchAnyColumn(null, 1, default, default, 2, null, null, 3)`Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 08:54:12

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

 Posted - 07/05/2006 :  09:07:44 Would this be sufficient??``` Declare @t table (id int identity(1,1), col1 int, col2 int, col3 int) Insert Into @t Select 1,2,3 Union All Select 2,1,3 Union All Select 3,2,1 Union All Select 1,5,6 Union All Select 6,6,4 Select * From @t Declare @col1 int, @col2 int, @col3 int Select @col1 = 3, @col2 = 2, @col3 = 1 Select * From @t Where col1 in (@col1,@col2,@col3) and col2 in (@col1,@col2,@col3) and col3 in (@col1,@col2,@col3) ```CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  10:00:52 Really nice and elegant solution, if there were only 3 columns in original table, I agree.There are 8 columns. The three search values can match any of the 8 columns.You would have to add something like```Where (col1 in (@col1,@col2,@col3) and col2 in (@col1,@col2,@col3) and col3 in (@col1,@col2,@col3)) or (col1 in (@col1,@col2,@col3) and col2 in (@col1,@col2,@col3) and col4 in (@col1,@col2,@col3)) or (col1 in (@col1,@col2,@col3) and col2 in (@col1,@col2,@col3) and col5 in (@col1,@col2,@col3))```to the where clause.And since there are 8 columns and only 3 search values, you would end up with 8 * 7 * 6 (permutation) = 336 where's or or's to cover all bases. And to delimit all duplicates, divide by 6 (3! faculty) and here is 56 possible combinations.And I am sorry to say that your logic will catch faulty rows. Imagine you have search values 1, 2 and 3.Now think what will happen when parsing a row with values 1, 1, and 1.Or if there is a row with 1, 43 and 99 and searching for 1, 2 and 4.Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 17:19:28

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

 Posted - 07/05/2006 :  10:13:38 Then how about this...?``` Declare @t table (id int identity(1,1), col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int) Insert Into @t Select 1,2,3,4,5,6,7,8 Union All Select 2,1,3,4,5,6,7,8 Union All Select 3,2,1,4,5,6,7,8 Union All Select 1,5,6,3,4,7,8,9 Union All Select 6,6,4,5,5,5,5,5 Union All Select 6,6,4,5,5,3,1,2 Select * From @t Declare @col1 int, @col2 int, @col3 int Select @col1 = 3, @col2 = 2, @col3 = 1 /*--test 2 Select @col1 = 5, @col2 = 5, @col3 = 1 */ Declare @s table (id int identity(1,1), s int) Insert Into @s Select s = @col1 Union All Select s = @col2 Union All Select s = @col3 Select t.* From @t t Inner Join ( Select A.id From ( Select distinct t.id From @t t Inner Join @s s On t.col1 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col2 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col3 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col4 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col5 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col6 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col7 = s.s Union All Select distinct t.id From @t t Inner Join @s s On t.col8 = s.s ) A Group By A.id Having count(*)=3 ) filtered On t.id = filtered.id ```CoreyCo-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  10:22:26 Great! Now that's an excellent query and not an god-awful hack The only drawback I can see, is that there is an identity column. But hey, so was I asking for too...Not even is the code cleaner and easier to maintain, it is also 3 times faster And with one change`Having count(*) = 3`to`Having count(*) >= (select count(s) from @s)`your code accepts any number of search values as well.Thank you. I will use this in my application too.PS. Do you have the time to look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68606?Peter LarssonHelsingborg, Sweden Edited by - SwePeso on 07/05/2006 14:34:38

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

 Posted - 07/05/2006 :  11:16:59 How about NORMALIZING YOUR DATA!? Then the coding is much simpler...

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 07/05/2006 :  11:55:48 I agree with you!But you know, the customers only pay me to fix their immediate needs, not redesigning all their old databases.Peter LarssonHelsingborg, Sweden

X002548
Not Just a Number

15586 Posts