Simple Query

20 Oct 2005 3:07 PM

kiran

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

20 Oct 2005 3:43 PM

Tarik

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

Show quote

"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
>

20 Oct 2005 3:52 PM

Bob Barrows [MVP]

kiran wrote:
Show quote

> 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.

20 Oct 2005 4:45 PM

kiran

Bob Barrows [MVP] wrote:
Show quote

> 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
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

20 Oct 2005 5:52 PM

Bob Barrows [MVP]

kiran wrote:
Show quote

> Bob Barrows [MVP] wrote:
>>
>> 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
>

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.

20 Oct 2005 6:15 PM

kiran

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

20 Oct 2005 6:57 PM

Bob Barrows [MVP]

kiran wrote:
Show quote

> 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
>

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.

21 Oct 2005 6:32 AM

kiran

Bob Barrows [MVP] wrote:
Show quote

> kiran wrote:
>
>>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
>>
>
>
> where COALESCE(t2.col1, @empid) =1

Thanks a lot Bob