SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Algorithm		  Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 4

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  03:09:52  Show Profile
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,3
2,1,3
3,2,1
1,5,6
6,6,4

If the user search for connection 3,2,1 I should return following rows
1,2,3
2,1,3
3,2,1

3,2,1 is the same with combination of all 3

Anyone has an idea?

I thought to use some sum row, but it won'r help because
1,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
29138 Posts
Posted - 07/05/2006 :  03:24:25  Show Profile  Visit SwePeso's Homepage
Take a look at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66605


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  03:39:15  Show Profile
Thank you for help.
But I have found an easy but effective solution

SELECT COL1,COL2,COL3 FROM
(SELECT COL1, COL2,COL3, COL1+COL2+COL3 AS COL_SUM, COL1*COL2*COL3 AS COL_MUL FROM TABLE_X) AS TEMP
WHERE TEMP.COL_SUM=INPUT1+INPUT2+INPUT3 AND TEMP.COL_MUL=INPUT1*INPUT2*INPUT3

When 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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  04:08:36  Show Profile  Visit SwePeso's Homepage
Are there always three search numbers? What if you search for 1 and 6?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  04:32:44  Show Profile
Yees, there must be always 3 search numbers. Also the numbers are >0.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  04:36:14  Show Profile  Visit SwePeso's Homepage
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 product
3	6	8
4	4	9
Sum is 17, product is 144.


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 04:38:40
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  04:42:42  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 04:46:53
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  04:43:05  Show Profile
:( You're right, I thought that I found a solution to quickly.
Do you have some idea?
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  04:47:31  Show Profile
Peter, thanks for help!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  05:04:44  Show Profile  Visit SwePeso's Homepage
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 product
1	6	6
2	2	9

2	6	6
3	3	8
Good luck!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  07:29:33  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  07:51:34  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 07:59:15
Go to Top of Page

zurbum
Yak Posting Veteran

Norway
55 Posts
Posted - 07/05/2006 :  08:06:44  Show Profile
Tack Peter!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  08:21:06  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 08:54:12
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 07/05/2006 :  09:07:44  Show Profile  Visit Seventhnight's Homepage
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)


Corey

Co-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 ..."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  10:00:52  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 17:19:28
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 07/05/2006 :  10:13:38  Show Profile  Visit Seventhnight's Homepage
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


Corey

Co-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 ..."
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  10:22:26  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden
Edited by - SwePeso on 07/05/2006 14:34:38
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 07/05/2006 :  11:16:59  Show Profile
How about NORMALIZING YOUR DATA!? Then the coding is much simpler...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/05/2006 :  11:55:48  Show Profile  Visit SwePeso's Homepage
I agree with you!

But you know, the customers only pay me to fix their immediate needs, not redesigning all their old databases.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 07/05/2006 :  12:08:08  Show Profile
quote:
Originally posted by blindman

How about NORMALIZING YOUR DATA!? Then the coding is much simpler...


And you thought he was blind.....



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000