outer join quesiont, pls help!

I have two tables. One (table1) is look up table that has 48 records for tim
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:

> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!

out parameter

hello all,
i am trying to get a returned value from a SP using an out parameter.
my code is in C# and i need some help (pls).
be greatfull if you send an example of the SP and the C# code that execute
it and collect the returning parameter value.
thanks a lot, Ran.
I don't think this has a C# example but it does explain OutPut parameters:
http://www.support.microsoft.com/?id=262499
Andrew J. Kelly SQL MVP
"Ran Y" <ranyc@.012.net.il> wrote in message
news:OjMSGU$PEHA.3456@.TK2MSFTNGP11.phx.gbl...
> hello all,
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
> thanks a lot, Ran.
>
|||[posted and mailed, please reply in news]
Ran Y (ranyc@.012.net.il) writes:
> i am trying to get a returned value from a SP using an out parameter.
> my code is in C# and i need some help (pls).
> be greatfull if you send an example of the SP and the C# code that execute
> it and collect the returning parameter value.
You did not specify which .Net Data Provider you are using, so I am
assuming SqlClient. Furthermore, the code I have around is VB.Net,
so you will need to transliterate in to C# on your own:
Dim p As SqlParameter = New SqlParameter
p.ParameterName = "@.outparam"
p.DbType = SqlDbType.Int ' For instance.
p.Direction = ParameterDirection.InputOutput
p.Value = DBNull.Value
cmd.Parameters.Add(p)
cmd.ExecuteNonQuery ' Or .Fill or an .ExecuteReader loop
The value of @.outparam is now in p.Value.
There are a couple of variations on how you can create the
parameter, see the online documentation for this.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Other than UNION

Hello,

Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)
solution than UNION operator in this case. TIA.

-- DDL
CREATE TABLE #TMP (col varchar(10));

-- DML
insert into #TMP
values('A124');

insert into #TMP
values('A127');

insert into #TMP
values('A12728');

insert into #TMP
values('A17282');

insert into #TMP
values('BCD');

insert into #TMP
values('BCD');

insert into #TMP
values('CDSS');

insert into #TMP
values('DS');

insert into #TMP
values('YUUEI');

-- goal: get one row with col data starting with 'A' and distict rows
for the rest

select top 1 col
from #TMP
where col LIKE 'A%'

UNION

select distinct col
from #TMP
where col NOT LIKE 'A%'On 16 Mar 2005 11:19:33 -0800, NickName wrote:

>Bear with me (not had much sleep last night), pls see following ddl,
>dml and comments for what is desired, I don't have a problem getting
>desired result(s), however, I'm wondering if there's another (better)
>solution than UNION operator in this case. TIA.

Hi Nick,

Well, using UNION ALL instead of UNION would be the obvious improvement.
Or you could use the following (completely different) approach. Test it
in your environment to see which version performs best.

select distinct col
from #TMP AS t1
where col not like 'A%'
or not exists (select *
from #TMP AS t2
where col like 'A%'
and t2.col < t1.col)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the response, Hugo. I was not paying enough attention. The
"DISTINCT" key word is not necessary in previous posting because UNION
would return unique records anyway, as for UNION ALL, it does not seem
applicable here because it would return duplicate rows as well, which
was/is not desired.

Don

Hugo Kornelis wrote:
> On 16 Mar 2005 11:19:33 -0800, NickName wrote:
> >Bear with me (not had much sleep last night), pls see following ddl,
> >dml and comments for what is desired, I don't have a problem getting
> >desired result(s), however, I'm wondering if there's another
(better)
> >solution than UNION operator in this case. TIA.
> Hi Nick,
> Well, using UNION ALL instead of UNION would be the obvious
improvement.
> Or you could use the following (completely different) approach. Test
it
> in your environment to see which version performs best.
> select distinct col
> from #TMP AS t1
> where col not like 'A%'
> or not exists (select *
> from #TMP AS t2
> where col like 'A%'
> and t2.col < t1.col)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On 16 Mar 2005 18:16:12 -0800, NickName wrote:

>Thanks for the response, Hugo. I was not paying enough attention. The
>"DISTINCT" key word is not necessary in previous posting because UNION
>would return unique records anyway, as for UNION ALL, it does not seem
>applicable here because it would return duplicate rows as well, which
>was/is not desired.

Hi Don,

In your original code, your first query will always return one row, so
it can't have duplicates; the second uses DISTINCT to eliminate
duplicates. But since you don't use UNION ALL, the server still has to
check if the row from the first result set is also in the second result
set, and eliminate it if it is. You and I know that this can never
happen (as a result of mutual exclusive WHERRE clauses), so we can save
the server some work by adding ALL to the UNION keyword.

If you remove the DISTINCT from the second query, then you must indeed
use UNION, not UNION ALL. You still might to check if there's a big
difference in execution times (probably not, but you never know). Oh,
and do include my UNION-less version in your performance tests as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo, I appreciate your opinion. As my original posting indicated
"I was not in thinking mode when I composed the msg", for just now when
I took a closer look, I notice that the data set is small, hence,
almost "any" query would do the job well, not really necessarily for
optimization, Query Analyzer wasn't even able to calculate execution
time, however, for large data set, your other approach would be
meaningful to try. Again thanks.

Don