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)
 and operation on single columns

Author  Topic 

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 00:05:16

I got a different requirement as, i want to perform and operation on a single column. for ex:
select * from table1 where subject='java' and subject ='sql'
please tell me if anybody knows this problem. Answer most urgent...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 00:41:29
OR operation or AND operation ?

select * from table1 where subject='java' and OR subject ='sql'




KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 00:51:31
Thank u for responding Khtan. I am getting results of OR. But i want AND operation on single column. If possible please give me ur suggestion.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 00:58:52
if you wanted a AND, there will not be any result return.

Maybe you can post same sample data and the result that you want.


KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 01:03:09
Ya, ur correct khtan. But my friends said to me is, there must be chance for retrieving data when we imposing AND condition. Can we do with T-SQL. If yes please tell me. A great Humble thanks from me.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-14 : 01:10:47
Yes you can do it with T-SQL. But you must show us example data where you can have two different subjects. Are they in different rows? Or are they in the same column, but perhaps comma separated? Please show us an example.

There's no way that your question is urgent unless this is for a homework assignment that you waited to the last second to work on. We answer questions here for free, so please be respectful of that. Don't say your question is urgent when it's not.

Tara Kizer
Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 01:19:03
Hellow Tara Kizer,
I didnt understand y ur think this problem is not urgent to me.
For this point only i stopped for releasing my project. When we move to our scene. I need AND operation on SAME COLUMN. Please specify T-SQL statement according to my problem.Thanking u..
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 01:24:58
quote:
Originally posted by vsureshbabu

Hellow Tara Kizer,
I didnt understand y ur think this problem is not urgent to me.
For this point only i stopped for releasing my project. When we move to our scene. I need AND operation on SAME COLUMN. Please specify T-SQL statement according to my problem.Thanking u..



Please specify the table structure, sample data and result that you wanted as per requested by me & Tara. Thank you


KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 01:41:30
the table name me tbl_projects worked in that PlatForm varchar(50) in that

Platform EXperiance
c++ 1year
java 2years
aps 1.5years
.net 3 years

i want retrive data from tbl_projects, expericence which r having platform knowledge of C++ and Java..please tell me in T-SQL statement.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 02:26:58
So how is the result like ?


KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 02:31:52
i want retrive allrows which r having platform knowledge of C++ and Java..
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 02:37:16
quote:
Originally posted by vsureshbabu

i want retrive allrows which r having platform knowledge of C++ and Java..



So you wanted the 2 records in red ?
quote:

Platform EXperiance
c++ 1year
java 2years
aps 1.5years
.net 3 years




select * from tbl_projects
where Platform = 'c++'
or Platform = 'java'


This is exactly what i posted earlier.


KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 02:45:03
Are boss..

I know that statement. But i want AND operation on that same field.
Plz. tell me and operation..
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 02:50:58
You still have not tell us what do you want as a result ?

From the sample data you supplied, which are the records that you wanted ?





KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 04:10:11
OR IS THIS WHAT YOU WANT ?
quote:
AND operation on a single column


declare @candidate table
(
Candidate varchar(10),
Platform varchar(10),
EXperiance varchar(10)
)

insert into @candidate
select 'suresh', 'c++', '1year' union all
select 'suresh', 'java', '2years' union all
select 'suresh', 'aps', '1.5years' union all
select 'suresh', '.net', '3 years' union all
select 'babu', 'sql', '1year' union all
select 'babu', 'java', '2years' union all
select 'babu', 'aps', '1.5years' union all
select 'babu', '.net', '3 years'

select *
from @candidate c
where c.Candidate in (select c.Candidate
from @candidate c
where c.Platform in ('c++', 'java')
group by c.Candidate
having count(*) = 2
)
and c.Platform in ('c++', 'java')

/*
Candidate Platform EXperiance
---------- ---------- ----------
suresh c++ 1year
suresh java 2years
*/



KH

Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 04:24:11
Ya ur correct. But ur using 'in' operator. 'IN' operator means nothing but 'OR' right. then how u inserted AND condition in that.
Please tell clearly...
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 04:34:57
quote:
Ya ur correct.

Then why don't you say so in the first place.

quote:
'IN' operator means nothing but 'OR' right. then how u inserted AND condition in that.

Yes 'IN' can be replaced by 'OR'. The effect of the query is what you called 'AND' on single column.

Must you have the 'AND' operator there in the query ?
select * from table1 where subject='java' and subject ='sql'

Any logical people will know that the above will not yield any result.


KH

Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-14 : 05:04:01
Let me guess!

declare @candidate table
(
Candidate varchar(10),
Platform varchar(10),
EXperiance varchar(10)
)

insert into @candidate
select 'aaa', 'c++', '1year' union all
select 'aaa', 'java', '2years' union all
select 'aaa', '.net', '3 years' union all
select 'bbb', 'sql', '1year' union all
select 'bbb', 'java', '2years' union all
select 'ccc', 'c++', '1.5years' union all
select 'ccc', '.net', '3 years' union all
select 'ddd', 'java', '4 years' union all
select 'ddd', 'c++', '2 years' union all
select 'ddd', '.net', '3 years'




Now do you mean to say that you want to find out people who have opted for both the courses (JAVA and C++) and not just either of them?

So the required output here will be:

Candidate  Platform   EXperiance 
---------- ---------- ----------
aaa c++ 1year
aaa java 2years
ddd java 4years
ddd c++ 2years


Is that right?


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

vsureshbabu
Starting Member

10 Posts

Posted - 2007-02-14 : 06:01:25
Ya harsha. I want pool the persons who r having platform experiance in JAVA AND C++.I dont want or operation , i need AND operation on platform field...
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-14 : 06:11:16
How about this?

declare @candidate table
(
Candidate varchar(10),
Platform varchar(10),
EXperiance varchar(10)
)

insert into @candidate
select 'aaa', 'c++', '1year' union all
select 'aaa', 'java', '2years' union all
select 'aaa', '.net', '3 years' union all
select 'bbb', 'sql', '1year' union all
select 'bbb', 'java', '2years' union all
select 'ccc', 'c++', '1.5years' union all
select 'ccc', '.net', '3 years' union all
select 'ddd', 'java', '4 years' union all
select 'ddd', 'c++', '2 years' union all
select 'ddd', '.net', '3 years'


select *
from @candidate t1
Join
(
select candidate from @candidate
where Platform in ('C++', 'Java')
group by candidate
having count(*) = 2 ) as t2
on t1.Candidate = t2.Candidate
where Platform in ('C++', 'Java')


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:13:59
Both criterias fulfilled!
quote:
Originally posted by vsureshbabu

But i want AND operation on single column.
-- prepare sample data
declare @candidate table
(
Candidate varchar(10),
Platform varchar(10),
EXperiance varchar(10)
)

insert into @candidate
select 'aaa', 'c++', '1year' union all
select 'aaa', 'java', '2years' union all
select 'aaa', '.net', '3 years' union all
select 'bbb', 'sql', '1year' union all
select 'bbb', 'java', '2years' union all
select 'ccc', 'c++', '1.5years' union all
select 'ccc', '.net', '3 years' union all
select 'ddd', 'java', '4 years' union all
select 'ddd', 'c++', '2 years' union all
select 'ddd', '.net', '3 years'

-- Have more fun!
select c1.candidate,
c1.platform,
c2.platform
from @candidate as c1
inner join @candidate as c2 on c2.candidate = c1.candidate
where c1.platform = 'sql'
and c2.platform = 'java'
I hope you get a good grade from your professor.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -