extracting some keys by pattern

[buf]

Hello everybody.
Help me please,
My table consists of one big key = some subkeys(key1,key2, ..),id and some other information.
=================================
|id|key1|key2|key3|key4|info|...
=================================
| 1| k1 | k2 | k3 | k4 | |
| 2| k1 | k2 | k5 | k4 |
......

I want to extract all 4 keys using some patern
e.g.
get list of keys 'key1' with other corresponding keys, where others - key2 like 'a%' AND key3 like 'b%' AND key4 like 'c%' in one SQL query..
but I don't know how
plz help,
10x in advance

 

[swampBoogie]

Can't say that I understand anything. When you talk about kkey2 like 'a%' whereas your data shows k2 I got lost.

Give some real sample data and the expected result.

 

[buf]

|id|key1|key2|key3|key4|
========================
|1 |k1 |k1 |k1 |k1 |
|3 |k2 |k2 |k3 |k4 |
|8 |k1 |k1 |k3 |k4 |
|0 |k1 |a1 |a2 |a4 |
|4 |k1 |k1 |b4 |b4 |
|5 |k0 |a0 |b0 |c0 |
|2 |k0 |k3 |b0 |c0 |
........................
========================
key1,key2,key3,key4 are not unique,
but {key1,key2,key3,key4} is.
here is samples :
query1:
get ONE {key1,key2,key3,key4} where key1 like 'k1' AND key2 like '%' AND key3 like '%' AND key4 like '%';
=> possible returns:
1) k1 k1 k1 k1 (id=1)
or 2) k1 k1 k3 k4 (id=8)
or 3) k1 a1 a2 a4 (id=0)
or 4) k1 k1 b4 b4 (id=4)

query 2:
get ALL {key1,key2,key3,key4} where key1 like 'k1' AND key2 like '%' AND key3 like '%' AND key4 like '%';
=> possible return:
1) k1 k1 k1 k1 (id=1)
k1 k1 k3 k4 (id=8)
k1 a1 a2 a4 (id=0)
k1 k1 b4 b4 (id=4)

query 3:
get key1 LIST + supplimented key2,key3,key4 where key2 like 'k%' AND key3 like '%' AND key4 like '%';
=> possible returns:
1) k1 k1 k1 k1
k2 k2 k3 k4
k0 k3 b0 c0
or
2) k2 k2 k3 k4
k1 k1 k3 k4
k0 k3 b0 c0
or
3) k2 k2 k3 k4
k1 k1 b4 b4
k0 k3 b0 b0

so, like this..

 

[swampBoogie]

No, this is beyond me.

 

[dnoeth]

Far beyond me, too ;-)
I've heard lots of confusing descriptions, but this one...

Dieter

 

[tlbroadbent]

I, too, find the description of the problem difficlt to understand. I did produce he following queries which seem to produce one of the listed outputs in each category.

Is this what you seek or can you further clarify your need? Perhaps, you can show queries that you've written and tell us whether they work or not. If they don't work, explain how the output differs from what you want.
[tt]
--Query 1:
Select * From YourTable
Where Key1 = 'K1' -- Or Key1 Like 'K1%'
And ID=(Select Min(ID) From @YourTable Where Key1='k1')

--Query 2:
Select * From YourTable
Where Key1 = 'K1' -- Or Key1 Like 'K1%'

--Query 3:
Select q1.ID, q1.Key1, q2.Key2, q2.key3, q2.key4
From (Select Key1, Key2, Max(ID) As ID From YourTable
Group By Key1, Key2) As q1
Join (Select ID, Key1, Key2, Key3, Key4 From YourTable Where Key2 Like 'K%') As q2
On q1.ID=q2.ID[/tt] If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[buf]

ok,
thanks all for your answers.
now I've understood,
and now there is no problem
10x alot