Different conditions with same value

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

SQL Server 2005 Forums

Transact-SQL (2005)

Different conditions with same value

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-15 : 16:31:19

Hi!

I have an table like this

CREATE TABLE #report
(
[name] nvarchar(255),
[group] nvarchar(255),
[min] float,
[max] float
)

Then i put some data into it
INSERT INTO #report([name],[group],[min],[max])
VALUES ('A','g1','2','4')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('B','g1','1','14')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('C','g2','3','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('D','g2','5','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('E','g2','12','7')

i want to handle a query like this

SELECT * FROM [#minmax_report]
WHERE
(([min] > 3 AND [max] < 8) AND [group] = 'g1' )
AND
(([min] > 4 AND [max] < 8) AND [group] = 'g2' )

I Know now, that i can't have [group] = 'g1' in the same time as [group] = 'g2'

This wouldn't of course give any result, But i want to handle
every group on its own in those conditions i have above, and then put some AND operation between those result i got, and see which [name] do i got from the result. So i don't know how to figure out this yet.

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-15 : 16:35:05

Maybe you mean this?

SELECT * FROM [#minmax_report]
WHERE 
(([min] > 3 AND [max] < 8) AND [group] = 'g1' )
OR
(([min] > 4 AND [max] < 8) AND [group] = 'g2' )

No, you're never too old to Yak'n'Roll if you're too young to die.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-15 : 16:44:41

Thanks for answer,

im afraid this isn't what i where searching for.
Maby this is a better hint

Image this
SELECT * FROM [#report]
WHERE (([min] > 3 AND [max] < 8) AND [group] = 'g1' )

And then we have separate
SELECT * FROM [#report]
(([min] > 4 AND [max] < 8) AND [group] = 'g2' )

now both those has to have some result to succeed the condition

Res1 AND Res2

if they do, all records in Res1 that fulfill the first condition will be show
and so will the Res2 with there records that is fulfill theres conditions.

So in the state above i would aspect to have as result

'A','g1','2','4'
'D','g2','5','7'

But if would have change the second condition (Res2) to
(([min] > 4 AND [max] < 5) AND [group] = 'g2' )

which would give nothing than
it dosen't matther anymore if Res1 is true or not
we still got

Res1 AND Res2 = False

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-15 : 17:22:03

Ok i find an solution now

look here

CREATE TABLE #report
(
[name] nvarchar(255),
[group] nvarchar(255),
[min] float,
[max] float
)

Then i put some data into it
INSERT INTO #report([name],[group],[min],[max])
VALUES ('A','g1','2','4')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('B','g1','1','14')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('C','g2','3','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('D','g2','5','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('E','g2','12','7')

SELECT [#minmax_report].* FROM [#report]
WHERE
EXISTS
(
SELECT * FROM [#report]
WHERE (([min] > 3 AND [max] < 8) AND [group] = 'g1' )
) AND
EXISTS
(
SELECT * FROM [#report]
(([min] > 4 AND [max] < 8) AND [group] = 'g2' )
)
AND
(
(([min] > 3 AND [max] < 8) AND [group] = 'g1' )
OR
(([min] > 4 AND [max] < 8) AND [group] = 'g2' )
)

But i really don't like it, maby someone can find an better solution??

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-15 : 17:35:46

What is the expected output from you query based on the sample data you provided? The syntax is not correct so I cannot run it and when I try to fix it I do not get any results; either becuase I do not understand the logic or your query/sample data do not match.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-17 : 07:42:33

First, Here is now the correct query, i was missing something before, but this
one has been tested, and it work as i want.

IF OBJECT_ID('tempdb..#report') IS NOT NULL
DROP TABLE tempdb..#report;
GO

CREATE TABLE #report
(
[name] nvarchar(255),
[group] nvarchar(255),
[min] float,
[max] float
)

INSERT INTO #report([name],[group],[min],[max])
VALUES ('A','g1','2','4')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('B','g1','1','14')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('C','g2','3','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('D','g2','5','7')
INSERT INTO #report([name],[group],[min],[max])
VALUES ('E','g2','12','7')

SELECT [#report].* FROM [#report]
WHERE
EXISTS
(
SELECT [name] FROM [#report]
WHERE [group] = 'g1' AND ([min] > 0)
) AND
EXISTS
(
SELECT [name] FROM [#report]
WHERE [group] = 'g2' AND ([min] > 4 )
)
AND
(
(([min] > 0) AND [group] = 'g1')
OR
(([min] > 4) AND [group] = 'g2' )
)

But im still worried on whats happening if got many more conditions or records,
Im not sure if this is the best solution.

What i want:

I want to perform this kind of query

SELECT * FROM #report
WHERE
(([min] > 0) AND [group] = 'g1')
AND
(([min] > 4) AND [group] = 'g2' )

And it can't as you see, so i then did then tried the solution above that that perfoming this behaviour.

How could this than been optimized?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:24:51

so you just want to see if there are records satisfying conditions [group] = 'g1' AND ([min] > 0) & [group] = 'g2' AND ([min] > 4 ) and only if both categories exist, you want all of them?

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-17 : 18:42:30

Hi Visakh16

Only if both categories condition is true, than show all the component that satisfied the condition for group g1 AND in the same time the components that satisfied the condition for g2.
It dosen't matter if one condition is true, both has to be true.

My solution works above, but im not satisifed with it, im convince it could be and should be optimized somehow, but have no idea how to implement that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-18 : 11:23:59

quote:
Originally posted by voyager838

<snip>
SELECT * FROM #report
WHERE
(([min] > 0) AND [group] = 'g1')
AND
(([min] > 4) AND [group] = 'g2' )

And it can't as you see, so i then did then tried the solution above that that perfoming this behaviour.

How could this than been optimized?

Why can't you change the AND to an OR?

SELECT * 
FROM #report
WHERE 
	([min] > 0 AND [group] = 'g1') 
	OR ([min] > 4 AND [group] = 'g2')

It returns the same results as the query you do not like.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-05-20 : 06:26:02

Hi Lamprey

Good questions.

The thing why not an single OR-operation would lead this to right subset
is that i only want to show the result from the set
when the both conditions are satisfied on the same time.

Try change the conditions on your query to

SELECT *
FROM #report
WHERE
([min] > 2 AND [group] = 'g1')
OR ([min] > 4 AND [group] = 'g2')

And than change every [min] >0 in my query to [min] > 2
and you see how it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources