| Author |
 Topic  |
|
vsureshbabu
Starting Member
India
10 Posts |
 Posted - 02/14/2007 : 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)
Singapore
16769 Posts |
Posted - 02/14/2007 : 00:41:29
|
OR operation or AND operation ?
select * from table1 where subject='java' and OR subject ='sql'
KH
|
 |
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35017 Posts |
Posted - 02/14/2007 : 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 |
Edited by - tkizer on 02/14/2007 01:12:31 |
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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.. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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.
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 02:26:58
|
So how is the result like ?
KH
|
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 02:31:52
|
| i want retrive allrows which r having platform knowledge of C++ and Java.. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 02:45:03
|
Are boss..
I know that statement. But i want AND operation on that same field.
Plz. tell me and operation.. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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... |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 02/14/2007 : 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
|
Edited by - khtan on 02/14/2007 04:38:19 |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/14/2007 : 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" |
|
|
|
vsureshbabu
Starting Member
India
10 Posts |
Posted - 02/14/2007 : 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... |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 02/14/2007 : 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" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 02/14/2007 : 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 |
Edited by - SwePeso on 02/14/2007 09:19:40 |
|
|
|
Topic |
|
and operation on single columns
