a difficult? sql query (SQL Server) 100% STAR 2

[Zargo]

Hi all,

I have an huge table called link with the following content:

company account Codestart Codeend

100 515055 U UZZZZZ
100 515055 KG1L KG1L
100 515055 KGINT KGINT
100 515055 KG1A KG1A
100 515055 LG1 LG5
100 515055 800000 899999
100 515055 900000 999999

And i have an Codes table with the following content(example)

Code

1123
U1
LG4
ZG
845000
Z03

-----------------

I want to select all codes from the codes table for account 515055. Must check the Link table to check which codes i can use for account 515055. So the result must be(in our example):

U1
LG4
845000

I have tried a query like:

select code from codes where code >=
(SELECT codestart from link where account = '515055')
AND code <=
(SELECT codeend from link where account = '515055')

I couldnt use this because i have more values in the link table. How can i smartly build this query?

I have a possiblity to change the content of the tables(adding extra column to make it easier?!, changing the data?)

Who can give me the golden answer? A big star for the solution.

Thanks in advance.

Zargo

 

[vongrunt]

If I understand correctly...

select Code
from Codes
where exists
(	select *
	from link
	where account = 515055
	and Codes.Code between Codestart and Codeend
)

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Zathras]

This works for me:
[tt]
create table #Link
(company int,
account int,
codestart varchar(10),
codeend varchar(10))
create table #Codes (code varchar(10))
go
insert into #Link values (100,515055,'U','UZZZZZ')
insert into #Link values (100,515055,'KG1L','KG1L')
insert into #Link values (100,515055,'KGINT','KGINT')
insert into #Link values (100,515055,'KG1A','KG1A')
insert into #Link values (100,515055,'LG1','LG5')
insert into #Link values (100,515055,'800000','899999')
insert into #Link values (100,515055,'900000','999999')
insert into #Codes values ('1123')
insert into #Codes values ('U1')
insert into #Codes values ('LG4')
insert into #Codes values ('ZG')
insert into #Codes values ('845000')
insert into #Codes values ('Z03')
go

select distinct code
from #Link, #Codes
where account=515055
and code between codestart and codeend
go
[/tt]
Result:
[tt]
code
----------
845000
LG4
U1
[/tt]

 

[Zargo]

THANKS!!! but i have forgotten to tell something. I could only use the where clause with one column in our application.

For example:
(Select code from codes where CODE) XXXXXXi can change this part of the where clauseXXXXXXXXXXXXX

i couldn't change the part with the parenthesis. So the (select code from codes where code) is standard and i can only write after the where CODE clause, with for example:
where CODE in (select code ......)

I can only change the part AFTER where CODE. I know its a stupid application of our supplier. Is it possible to get the same results with changing only the part after "where code" clause?

TIA

 

[Zargo]

By the way, when im using a subquery with the BETWEEN clause i'm getting

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 

[vongrunt]

Post code here...

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Zargo]

Sorry Vongrunt i had write the statement not OK. Your query is good BUT:

select Code
from Codes
where exists
( select *
from link
where account = 515055
and Codes.Code between Codestart and Codeend
)

But i could only use

select Code
from Codes
where code XXXXXXXXXXXXXX

I must get the same result to write a statement in stead of the XXXXXXXXXX

Is that possible?

 

[vongrunt]

Can you do something very similar to what Zathras did:

select distinct C.code
from Codes C
inner join Link L on C.code between L.codestart and L.codeend
where [!]L.account=515055[/!]

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[vongrunt]

If you can't ... view then:

create view vLinkCodes
as
select L.account, C.code
from Link L
inner join Codes C on C.code between L.Codestart and L.Codeend
go

Testing:

select code
from vLinkCodes
where [!]account = 515055[/!]

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Zargo]

Im going to try the view. I will be back asap . . .

 

[Zargo]

Vongrunt and Zathras thanks a lot!!