problem in query.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem in query..

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-09-24 : 09:29:51

SELECT *
FROM Salary
-------- -----------
A1 100
A2 200
A3 300
A4 400
A5 500

i need to write a query to select salary < salary of "A3" without using subqueries

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-24 : 10:09:49

Why not using subqueries?

No, you're never too old to Yak'n'Roll if you're too young to die.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-24 : 10:21:18

declare @temp as float
select @temp = salary where empid='A1'
select * from salary where salary<@temp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-24 : 10:21:57

Here's one way. -- Asuming that the first column is a key


DECLARE @salVal MONEY

SELECT @salVal = [The2ndColumn] FROM [TheTable] WHERE [TheFirstColumn] = 'A3'

SELECT * FROM Salary WHERE [The2ndColumn] < @salVal

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-09-24 : 12:45:29

Hi all, Thank you very very much for your replies..

Actually there is no specific reason for not using subquery for above query but i am not able to do that without subquery.. and finally i tried with joins and did that..

Please see below solution.. please correct me if anything wrong..

create table #tmp (names varchar(50), salary int)

insert into #tmp
select 'A1', 100
union all
select 'A2', 200
union all
select 'A3', 300
union all
select 'A4', 400
union all
select 'A5', 500

select * from #tmp

select t3.*
from #tmp t1
left join (select names, salary from #tmp where names = 'A3') t2
on t1.names = t2.names
left join #tmp t3 on t3.salary < t2.salary
where t3.names is not null

drop table #tmp

Thanks..

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-09-24 : 13:02:11

If I were you, I'd use Transact Charlie's sugestion, it will likely have better performance. Check the execution plans though, you never know for certain unless you try.

Also make sure you index the salary column.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-09-24 : 13:59:20

yes.. i agree with your opinion..

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-24 : 14:34:41

I don't know if anyone can tell you if it's right or not...

What do you expect as a result set?

You want to see ???? where the salary (MAX, MIN, AVG???) of A3 where they are less than all other salaries???

I swear it's not a cultural thing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-09-25 : 02:03:21

I want in result set that.. i have name of customer..
i want list of customer who has less salary then passed customer's salary..

so for example if i pass A3 then A3 has 300 salary say for example..
then i want result set which includes salary less then 300 without subquery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources