Returning the value from different table 1

[pacificpear]

Sql server

I have two tables X and Z

TABLE X

HOST DEPT#
A 1
B 2
C 3
D NULL
E 4
F NULL
G 5
H 6
I NULL

Then i have the second table which have the combination of host in alphadetical order

subject

A,B
B,C
A,B,C
A,B,D
D,E
D,F,H
.
.
.
.

I need the folowing result

subject result

A,B 1
B,C 2
A,B,C 1
A,B,D 1
D,E 4
D,F,H 6

the subject from table Z check the correspinding value of the series of string ie a,b or d,e etc and return the value in the result column by looking at the X table. If the value of first string is null than it will go to the the second string and return that value

eg
D , F ,H this string will return the value of H as String D and String F are null. So the value is 6
I think I had made my requirement clear

Can anyone help
thanks in advance

 

[DrSql]

Try some what like this. I dont have any data to test.
select a.subject,
newhost (case when substring(b.subject,1,1)='A' then a.dept#
else '--'
end
( from table a, table b
where a.host=b.substring(subject,1,1)



Dr.Sql

 

[vongrunt]

Denormalized column (Y.subject) makes this problem a bit harder. At least for sample data, try:

select Y.subject, min(X.dept) as result
from X inner join Y on Y.subject like '%' + X.host + '%'
group by Y.subject

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

This should work too, I guess

select    a.subject, 
          b.Dept# result
from      TableA a 
          inner join TableB b
          on substring(subject, 1, 1) = b.host)

PS: Code not tested.

Regards,
AA

 

[vongrunt]

Last two rows (4, 6) won't match that way

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

oops, my bad. Thanks for pointing it out vongrunt!

 

[pacificpear]

Hi All
Thanks for the replies

Few things to clear

1. The result does not contain the minimum value. It contain the value of the first string for examople

for A,c the result should be 1
c,d the result should be 3
d,e,f the result should be 4 as the value of D is null

2. the values given to the host are not or may not be in the a number it can be alphabet too.

3.It should not take the minimum values


Thanks
waiting for reply

 

[vongrunt]

> 1. The result does not contain the minimum value. It contain the value of the first string for examople

"First" by exactly what order? Host column?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pacificpear]

evalue corresponding to the first host

if it a,b thenn in result the value should the value corresponding to A in table X

If B,c then in result the value shul be the value corresponding to b in table X

if D,E then in result the value should be the value as e as d is null
i hope i am clear on that

Thanks

PP

 

[amrita418]

If the subject for a particular row is
D, F, I, M, N and N is the only value that is not null, the query should return dept# for N?

 

[pacificpear]

yes it shold return the value of N

 

[amrita418]

Here is a solution using a small function.

create function dbo.GetDept(@subject varchar(10))
returns int
as 
begin
declare 
@v_flag int,
@v_result int
set @v_flag = 0
while @v_flag = 0
begin
    select    @v_result = Dept# 
    from      TableX 
    where substring(@subject, 1, 1) = host
    If (@v_result is null)
        set @subject = substring(@subject, charindex(',', @subject) + 1, len(@subject))
    else
        set @v_flag = 1
end
return @v_result
end

Once you create the function just call the function like this:

select subject, dbo.GetDept(subject) from TableZ

Regards,
AA

 

[pacificpear]

what is the table Z?

 

[pacificpear]

when i execute that it give the following result


A,B 1
B,C 2
A,B,C 1
A,B,D 1
D,E 0
D,F,H 0

last two reuslts are wrong ,
1. as D is null and e has the value 4 in the table x so the result should be 4
2. as D is null and f is null too in the table x so the result should be the value of H ie 6

 

[vongrunt]

Try this:

select Y.subject,
( select top 1 dept from X where Y.subject like '%' + X.host + '%' and  dept is not null order by X.host) as result
from Y

... or if data in Y.subject has variable-length (for example 'blah,foo') but comma-separated format is always respected:

select Y.subject,
( select top 1 dept from X where ','+Y.subject+',' like '%,' + X.host + ',%' and  dept is not null order by X.host) as result
from Y

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[pacificpear]

vongrunt/Amrita

Your code fine until teh string has value but when it comes to D or the null value the result also show the value of D or the null instead of E ie 4

 

[vongrunt]

Argh...

create table X (host char(1), dept# int)
insert into x values('A', 1   )
insert into x values('B', 2   )
insert into x values('C', 3   )
insert into x values('D', NULL)
insert into x values('E', 4   )
insert into x values('F', NULL)
insert into x values('G', 5   )
insert into x values('H', 6   )
insert into x values('I', NULL)

create table y (subject varchar(10))
insert into y values('A,B'  )
insert into y values('B,C'  )
insert into y values('A,B,C')
insert into y values('A,B,D')
insert into y values('D,E'  )
insert into y values('D,F,H')

select Y.subject,
( select top 1 dept# from X where Y.subject like '%' + X.host + '%' and dept# is not null order by X.host) as result
from Y

drop table x
drop table y

Returns expected results, right?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

I tested my code on the sample data you provided and it gives the expected results. Maybe you are not passing the right parameters or querying th right tables. (TableX is your X and TableZ is your Z table).

However, its a shame that I used a function when the desired output could be achieved with a simple query (like vongrunt did).

Good job Vongrunt!

Regards,
AA

 

[pacificpear]

Amrita/Vongrunt

Thanks a lot both of you. It worked now i will implement it on the real data

thanks

PP