Algorithm - SQL Server Forums

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

All Forums

Old Forums

CLOSED - General SQL Server

Algorithm

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,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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 03:24:25

Take a look at this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66605

Peter Larsson
Helsingborg, Sweden

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 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.

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 Larsson
Helsingborg, 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 product

3	6	8
4	4	9

Sum is 17, product is 144.

Peter Larsson
Helsingborg, 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 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

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 product

Good luck!

Peter Larsson
Helsingborg, 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 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

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
	)
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

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 @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 ..."

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 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

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 @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 ..."

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 change

Having count(*) = 3

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 [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68606[/url]?

Peter Larsson
Helsingborg, 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 Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-05 : 12:08:08

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources