April 11, 2003 at 6:37 am
Okay, I know this is a SQL Server forum but I thought this might be an interesting puzzle to see if there might be a better way. The following is a thread from a user on our internal IQ network where he is trying to create a Sybase query that is much easier (i.e., less code needed) in SQL Server. His post was followed by two responses. Anyone know of a better way to handle this? That is, one that would expand as columns are added?
==================================
I've created a table & values as follows:
create table Test (code char(10), value int)
insert Test values ('a', 1)
insert Test values ('a', 1)
insert Test values ('a', 2)
insert Test values ('a', 3)
insert Test values ('b', 1)
insert Test values ('b', 2)
insert Test values ('b', 2)
giving us the data:
code value
---------- -----
a 1
a 1
a 2
a 3
b 1
b 2
b 2
The following queries give the expected output:
select distinct value
from Test
where code = 'a'
value
-----
1
2
3
select distinct value
from Test
where code = 'b'
value
-----
1
2
Now I would like to combine these results so that the output is like:
valuea valueb
------ ------
1 1
2 2
3 <null>
In MSSQL, I can use something like:
select
a.value as valuea,
b.value as valueb
from
(select distinct value
from Test where code = 'a' ) a,
(select distinct value
from Test where code = 'b' ) b
where
a.value *= b.value
(I could use a SQL-92 type join above, but thought it was less confusing using the "*=" type join)
Do you know if I can produce this output using Sybase without having to use views?
-------------------------------------------------
select distinct a.value valuea, b.value valueb
from john a, john b
where a.value *= b.value
and a.code1 = 'a'
and b.code1 = 'b'
order by a.value
The "order by" is irrelevant - I just put it there so it comes out in the same order as you example.
-------------------------------------------------
This works okay until someone inserts another value for code 'b':
insert Test values ('b', 4)
Since the outer join was on code 'a' you won't see the above row. Unfortunately, this adds to the complexity of the query. I'm certain there is a better way but here is how I would expand upon your code:
select distinct a.value valuea, b.value valueb
from Test a, Test b
where a.value *= b.value
and a.code = 'a'
and b.code = 'b'
union
select distinct a.value valuea, b.value valueb
from Test a, Test b
where a.value =* b.value
and a.code = 'a'
and b.code = 'b'
Now, if you are looking to try and have this query order the results you probably would have to do something like this:
select distinct a.value valuea, b.value valueb, IsNull(b.value, a.value) ord_by
from Test a, Test b
where a.value *= b.value
and a.code = 'a'
and b.code = 'b'
union
select distinct a.value valuea, b.value valueb, IsNull(a.value, b.value) ord_by
from Test a, Test b
where a.value =* b.value
and a.code = 'a'
and b.code = 'b'
order by ord_by
Where this will get even more ugly is if you add a third code column or a fourth....
Anyone aware of a cleaner more expandable way to handle this?
April 11, 2003 at 7:20 am
Not sure if Sybase will handle correlated sub-queries but if it will, try the following;
select
distinct(Value) as AValue
, (
select distinct(value)
from Test b
where
Code = 'b'
and b.Value = a.Value
) as BValue
from Test a
where a.Code = 'a'
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 11, 2003 at 9:45 am
It did handle the correlated sub-query but the problem remains that it doesn't pick up the last insert for code b (insert Test values ('b', 4)) because it doesn't exist in your main query. So, it still seems that I need a union like this:
select
distinct(a.Value) as AValue,
(select distinct(b.value)
from Test b
where
Code = 'b'
and b.Value = a.Value) as BValue
from Test a
where a.Code = 'a'
union
select distinct
(select distinct(b.value)
from Test b
where
Code = 'a'
and b.Value = a.Value) as AValue,
(a.Value) as BValue
from Test a
where a.Code = 'b'
or start using temp tables...
create table #temp (AValue int null, BValue int null)
insert into #temp
select distinct value as AValue, convert(int, null) as BValue
from Test
where code = 'a'
update #temp
set BValue = b.value
from #temp t, Test b
where b.code = 'b'
and t.AValue = b.value
insert #temp
select distinct null, value
from Test b
where b.code = 'b'
and not exists (select 1 from #temp t where t.BValue = b.value)
select * from #temp
April 14, 2003 at 11:52 am
Sorry, missed the additional insert....
Try this;
select
b.Value
, d.Value
from
(select distinct(a.value) from Test a where a.Code = 'a') b
full outer join
(select distinct(c.value) from Test c where c.Code = 'b') d
on b.Value = d.Value
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 15, 2003 at 2:33 pm
Nope. That's where Sybase breaks. You can do the sub-query as a select element provided only one value is returned but you can't have it as your "table" definition (aka FROM reference). Also, Sybase only supports inner and left and right outer joins. Doesn't support full join.
This was actually a thread from someone in the firm that was trying to find a Sybase solution for something he could do in SQL Server. I couldn't think of a glamorous way of handling it in Sybase. Guess this explains one of the reasons I prefer working in SQL Server now though I definitely still do Sybase work.
Thanks for the try
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply