|
Home > Archive > MS SQL Server New Users > October 2005 > Simple Query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| Hi,
This looks simple but I couldn't get it right
For eg.
Table1 has
1 ABC
2 DEF
3 GHI
Table2 has
2 DEF
I need the result as(basically all the records of table1 and the
matching record of table2 must be at the top)
2 DEF
1 ABC
3 GHI
Any help will be appreciated
Thanks
Kiran
| |
|
| Hi ,
Try this :
create table #tmp_t
(
str0 char(3) ,
val0 integer
) ;
create table #tmp_t1
(
str1 char(3) ,
val1 integer
) ;
insert into #tmp_t values ('ABC' , 1 ) ;
insert into #tmp_t values ('DEF' , 2 ) ;
insert into #tmp_t values ('GHI' , 3 ) ;
insert into #tmp_t1 values ('DEF' , 2 ) ;
select
a.*
from
#tmp_t a left outer join #tmp_t1 b
on a.str0 = b.str1
and a.val0= b.val1
order by b.str1 desc
"kiran" wrote:
> Hi,
>
> This looks simple but I couldn't get it right
>
> For eg.
>
> Table1 has
>
> 1 ABC
> 2 DEF
> 3 GHI
>
> Table2 has
>
> 2 DEF
>
> I need the result as(basically all the records of table1 and the
> matching record of table2 must be at the top)
>
> 2 DEF
> 1 ABC
> 3 GHI
>
>
>
> Any help will be appreciated
>
> Thanks
> Kiran
>
| |
| Bob Barrows [MVP] 2005-10-27, 7:55 am |
| kiran wrote:
> Hi,
>
> This looks simple but I couldn't get it right
>
> For eg.
>
> Table1 has
>
> 1 ABC
> 2 DEF
> 3 GHI
>
> Table2 has
>
> 2 DEF
>
> I need the result as(basically all the records of table1 and the
> matching record of table2 must be at the top)
>
> 2 DEF
> 1 ABC
> 3 GHI
>
Does this make it simpler?
select t1.col1,t1.col2,t2.col1
from t1 left join t2 on t1.col1=t2.col1
order by t2.col1 DESC, t1.col1
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
| Bob Barrows [MVP] wrote:
> kiran wrote:
>
>
>
> Does this make it simpler?
>
> select t1.col1,t1.col2,t2.col1
> from t1 left join t2 on t1.col1=t2.col1
> order by t2.col1 DESC, t1.col1
>
> Bob Barrows
Hi Guys,
Thanks for the quick reply
that wasn't the problem, I found the problem
I too was using left outer join, what happened was
I had a column from table2 named empid so the result was correct when I
used left outer join
2 DEF 1
1 ADC NULL
3 GHI NULL
I had included a where condition "where empid=1", so it was returning only
2 DEF 1
I need all the three rows
I tried "empid=CASE WHEN empid=NULL THEN @empid ELSE empid END" this in
select statement
@empid here is 1
it returned this
2 DEF 1
1 ADC NULL
3 GHI NULL
but I need it as
2 DEF 1
1 ADC 1
3 GHI 1
so I can put that where condition
Thanks
Kiran
| |
| Bob Barrows [MVP] 2005-10-27, 7:55 am |
| kiran wrote:
> Bob Barrows [MVP] wrote:
> Hi Guys,
>
> Thanks for the quick reply
>
> that wasn't the problem, I found the problem
>
> I too was using left outer join, what happened was
>
> I had a column from table2 named empid so the result was correct when
> I used left outer join
>
> 2 DEF 1
> 1 ADC NULL
> 3 GHI NULL
>
> I had included a where condition "where empid=1", so it was returning
> only
>
> 2 DEF 1
>
> I need all the three rows
>
> I tried "empid=CASE WHEN empid=NULL THEN @empid ELSE empid END" this
> in select statement
>
>
> @empid here is 1
>
> it returned this
>
> 2 DEF 1
> 1 ADC NULL
> 3 GHI NULL
>
> but I need it as
>
> 2 DEF 1
> 1 ADC 1
> 3 GHI 1
>
> so I can put that where condition
>
I think you want to use the COALESCE function.
select t1.col1,t1.col2,COALESCE(t2.col1, @empid)
from t1 left join t2 on t1.col1=t2.col1
order by t2.col1 DESC, t1.col1
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
| Bob Barrows [MVP] wrote:
>
> I think you want to use the COALESCE function.
>
> select t1.col1,t1.col2,COALESCE(t2.col1, @empid)
> from t1 left join t2 on t1.col1=t2.col1
> order by t2.col1 DESC, t1.col1
>
>
Hi Bob,
the COALESCE function works fine but I still have the problem
I need to use that where condition that I specified
select t1.col1,t1.col2,COALESCE(t2.col1, @empid)
from t1 left join t2 on t1.col1=t2.col1 where empid=1
order by t2.col1 DESC, t1.col1
the above query returns only one row
2 DEF 1
I tried this too
select t1.col1,t1.col2,COALESCE(t2.col1, @empid) as empid_temp
from t1 left join t2 on t1.col1=t2.col1 where empid_temp=1
order by t2.col1 DESC, t1.col1
gives me an error
Invalid column name empid_temp
works fine without the where condition
Thanks
Kiran
| |
| Bob Barrows [MVP] 2005-10-27, 7:55 am |
| kiran wrote:
> Bob Barrows [MVP] wrote:
> Hi Bob,
>
> the COALESCE function works fine but I still have the problem
>
> I need to use that where condition that I specified
>
> select t1.col1,t1.col2,COALESCE(t2.col1, @empid)
> from t1 left join t2 on t1.col1=t2.col1 where empid=1
> order by t2.col1 DESC, t1.col1
>
> the above query returns only one row
>
> 2 DEF 1
>
> I tried this too
>
> select t1.col1,t1.col2,COALESCE(t2.col1, @empid) as empid_temp
> from t1 left join t2 on t1.col1=t2.col1 where empid_temp=1
> order by t2.col1 DESC, t1.col1
>
> gives me an error
>
> Invalid column name empid_temp
>
where COALESCE(t2.col1, @empid) =1
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
| |
|
| Bob Barrows [MVP] wrote:
> kiran wrote:
>
>
>
> where COALESCE(t2.col1, @empid) =1
Thanks a lot Bob
|
|
|
|
|