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 |
|
|
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. |
|
|
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 |
|
|
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. |
|
|
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 |
|
|
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.. |
|
|
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 |
|
|
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 thatPlatform EXperiancec++ 1yearjava 2yearsaps 1.5years.net 3 yearsi 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)
17689 Posts |
Posted - 2007-02-14 : 02:26:58
|
So how is the result like ? KH |
|
|
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.. |
|
|
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 EXperiancec++ 1yearjava 2yearsaps 1.5years.net 3 years
select * from tbl_projectswhere Platform = 'c++'or Platform = 'java' This is exactly what i posted earlier. KH |
|
|
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.. |
|
|
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 |
|
|
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 @candidateselect 'suresh', 'c++', '1year' union allselect 'suresh', 'java', '2years' union allselect 'suresh', 'aps', '1.5years' union allselect 'suresh', '.net', '3 years' union allselect 'babu', 'sql', '1year' union allselect 'babu', 'java', '2years' union allselect 'babu', 'aps', '1.5years' union allselect 'babu', '.net', '3 years'select *from @candidate cwhere 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++ 1yearsuresh java 2years*/ KH |
|
|
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... |
|
|
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 |
|
|
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 @candidateselect 'aaa', 'c++', '1year' union allselect 'aaa', 'java', '2years' union allselect 'aaa', '.net', '3 years' union allselect 'bbb', 'sql', '1year' union allselect 'bbb', 'java', '2years' union allselect 'ccc', 'c++', '1.5years' union allselect 'ccc', '.net', '3 years' union allselect 'ddd', 'java', '4 years' union allselect 'ddd', 'c++', '2 years' union allselect '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++ 1yearaaa java 2yearsddd java 4yearsddd c++ 2years Is that right?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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... |
|
|
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 @candidateselect 'aaa', 'c++', '1year' union allselect 'aaa', 'java', '2years' union allselect 'aaa', '.net', '3 years' union allselect 'bbb', 'sql', '1year' union allselect 'bbb', 'java', '2years' union allselect 'ccc', 'c++', '1.5years' union allselect 'ccc', '.net', '3 years' union allselect 'ddd', 'java', '4 years' union allselect 'ddd', 'c++', '2 years' union allselect 'ddd', '.net', '3 years'select * from @candidate t1Join ( select candidate from @candidate where Platform in ('C++', 'Java') group by candidate having count(*) = 2 ) as t2on t1.Candidate = t2.Candidatewhere Platform in ('C++', 'Java') Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
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 datadeclare @candidate table( Candidate varchar(10), Platform varchar(10), EXperiance varchar(10))insert into @candidateselect 'aaa', 'c++', '1year' union allselect 'aaa', 'java', '2years' union allselect 'aaa', '.net', '3 years' union allselect 'bbb', 'sql', '1year' union allselect 'bbb', 'java', '2years' union allselect 'ccc', 'c++', '1.5years' union allselect 'ccc', '.net', '3 years' union allselect 'ddd', 'java', '4 years' union allselect 'ddd', 'c++', '2 years' union allselect 'ddd', '.net', '3 years'-- Have more fun!select c1.candidate, c1.platform, c2.platformfrom @candidate as c1inner join @candidate as c2 on c2.candidate = c1.candidatewhere c1.platform = 'sql' and c2.platform = 'java' I hope you get a good grade from your professor.Peter LarssonHelsingborg, Sweden |
|
|
Previous Page&nsp;
Next Page
|