overlapping Query? badly needed!

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)

overlapping Query? badly needed!

Author

Topic

Honschu
Starting Member

3 Posts

Posted - 2011-01-10 : 05:06:19

Hi,

I have to query the following table:

ID - INT (Primary...)
EventID - INT
[Key] - nvchar
Value - mvchar

The structure is like a big array:

[EventID]
--- [Key]=>[Value]
--- [Key]=>[Value]
--- [Key]=>[Value]
--- [Key]=>[Value]
[EventID]
--- [Key]=>[Value]
--- [Key]=>[Value]
...

I want to run a query to a specific text in [Value] and refers to the corresponding outputs EventID all other entries.

like:

Select EventID
FROM Tabelle
WHERE
VALUE LIKE '%STRING%'

- but now all other entries with the same corresponding EventID

Sorry for my english, and thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-10 : 09:19:10

Honschu,

Could you give some sample data from Tabelle and what you'd like the output from your query to be?

Thanks,

Jim

Everyday I learn something that somebody else already knew

Honschu
Starting Member

3 Posts

Posted - 2011-01-10 : 09:47:42

for example:


EventID |  Key  | Value
---------------------------
22233   |  keya | some Text
22233   |  keyb | another String
22233   |  keyc | foo
22244   |  keya | some Text
22244   |  keyb | bar

Now I want to select a part like this:

SELECT * FROM Table WHERE Value LIKE'%foo%';

RESULT:
EventID |  Key  | Value
---------------------------
22233   |  keyc | foo

But I also want all other entries with this [EventID].
So the result should be:

EventID |  Key  | Value
---------------------------
22233   |  keya | some Text
22233   |  keyb | another String
22233   |  keyc | foo

Is this possible?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-10 : 09:49:35

SELECT *
FROM Table
WHERE EventID in (Select EventID from Table where Value like '%foo%')

Poor planning on your part does not constitute an emergency on my part.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-10 : 10:01:58

SELECT t1.*
FROM table t1
WHERE EXISTS (select 1 from table t2 where t1.eventid = t2.eventid and t2.value like '%foo%')

Jim

Everyday I learn something that somebody else already knew

Honschu
Starting Member

3 Posts

Posted - 2011-01-10 : 10:08:51

Tank you all and greetings from germany

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-10 : 12:35:20

Or, for fun, as a join:

SELECT A.* 
FROM Table AS A
INNER JOIN
    (
        Select EventID 
        from Table 
        where Value like '%foo%'
    ) AS B
ON A.EventID = B.EventID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources