Author |
Topic |
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 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. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 04:08:36
|
Are there always three search numbers? What if you search for 1 and 6?Peter LarssonHelsingborg, Sweden |
|
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 04:32:44
|
Yees, there must be always 3 search numbers. Also the numbers are >0. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 04:36:14
|
quote: Originally posted by zurbum Yees, 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 product3 6 84 4 9 Sum is 17, product is 144.Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 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 datadeclare @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 allselect 2, 1, 3 union allselect 3, 2, 1 union allselect 1, 5, 6 union allselect 6, 6, 4-- define search variablesdeclare @con1 int, @con2 int, @con3 int, @con4 int, @con5 int, @con6 int, @con7 int, @con8 intselect @con4 = 6, @con7 = 6declare @search table (id int identity(1, 1), con int)insert @searchselect @con1 where @con1 is not null union allselect @con2 where @con2 is not null union allselect @con3 where @con3 is not null union allselect @con4 where @con4 is not null union allselect @con5 where @con5 is not null union allselect @con6 where @con6 is not null union allselect @con7 where @con7 is not null union allselect @con8 where @con8 is not null -- do the search!select distinct t.*from @test tleft join @search s1 on t.con1 = s1.conleft join @search s2 on t.con2 = s2.conleft join @search s3 on t.con3 = s3.conleft join @search s4 on t.con4 = s4.conleft join @search s5 on t.con5 = s5.conleft join @search s6 on t.con6 = s6.conleft join @search s7 on t.con7 = s7.conleft join @search s8 on t.con8 = s8.conwhere 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 |
|
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 04:43:05
|
:( You're right, I thought that I found a solution to quickly.Do you have some idea? |
|
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 04:47:31
|
Peter, thanks for help! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 05:04:44
|
quote: Originally posted by zurbum Peter, 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 product1 6 62 2 92 6 63 3 8 Good luck!Peter LarssonHelsingborg, Sweden |
|
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 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 TABLEASBEGINdeclare @search table (id int identity(1, 1), con int)insert @searchselect @con1 where @con1 is not null union all -- This is the only select @con2 where @con2 is not null union all -- code needed toselect @con3 where @con3 is not null -- be changed!RETURN (select distinct mytable.*from mytable tleft join @search s1 on t.con1 = s1.conleft join @search s2 on t.con2 = s2.conleft join @search s3 on t.con3 = s3.conleft join @search s4 on t.con4 = s4.conleft join @search s5 on t.con5 = s5.conleft join @search s6 on t.con6 = s6.conleft join @search s7 on t.con7 = s7.conleft join @search s8 on t.con8 = s8.conwhere 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 |
|
|
zurbum
Yak Posting Veteran
55 Posts |
Posted - 2006-07-05 : 08:06:44
|
Tack Peter! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 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 )ASBEGIN 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) RETURNEND 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 DEFAULTSELECT * FROM dbo.fnSearchAnyColumn(null, 1, default, default, 2, null, null, 3) Peter LarssonHelsingborg, Sweden |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-07-05 : 09:07:44
|
Would this be sufficient??Declare @t table (id int identity(1,1), col1 int, col2 int, col3 int)Insert Into @tSelect 1,2,3 Union AllSelect 2,1,3 Union AllSelect 3,2,1 Union AllSelect 1,5,6 Union AllSelect 6,6,4Select * From @tDeclare @col1 int, @col2 int, @col3 intSelect @col1 = 3, @col2 = 2, @col3 = 1Select * From @tWhere 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
30421 Posts |
Posted - 2006-07-05 : 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 likeWhere (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 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-07-05 : 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 @tSelect 1,2,3,4,5,6,7,8 Union AllSelect 2,1,3,4,5,6,7,8 Union AllSelect 3,2,1,4,5,6,7,8 Union AllSelect 1,5,6,3,4,7,8,9 Union AllSelect 6,6,4,5,5,5,5,5 Union AllSelect 6,6,4,5,5,3,1,2Select * From @tDeclare @col1 int, @col2 int, @col3 intSelect @col1 = 3, @col2 = 2, @col3 = 1/*--test 2Select @col1 = 5, @col2 = 5, @col3 = 1*/Declare @s table (id int identity(1,1), s int)Insert Into @sSelect s = @col1 Union AllSelect s = @col2 Union AllSelect s = @col3Select t.* From @t tInner 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 ) filteredOn 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
30421 Posts |
Posted - 2006-07-05 : 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 changeHaving count(*) = 3 toHaving 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 [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68606[/url]?Peter LarssonHelsingborg, Sweden |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-07-05 : 11:16:59
|
How about NORMALIZING YOUR DATA!? Then the coding is much simpler... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-07-05 : 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 |
|
Next Page
|
|
|