SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 and operation on single columns
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

vsureshbabu
Starting Member

India
10 Posts

Posted - 02/14/2007 :  00:05:16  Show Profile  Reply with Quote

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

Posted - 02/14/2007 :  00:41:29  Show Profile  Reply with Quote
OR operation or AND operation ?

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




KH

Go to Top of Page

vsureshbabu
Starting Member

India
10 Posts

Posted - 02/14/2007 :  00:51:31  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  00:58:52  Show Profile  Reply with Quote
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

India
10 Posts

Posted - 02/14/2007 :  01:03:09  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 02/14/2007 :  01:10:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vsureshbabu
Starting Member

India
10 Posts

Posted - 02/14/2007 :  01:19:03  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  01:24:58  Show Profile  Reply with Quote
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

India
10 Posts

Posted - 02/14/2007 :  01:41:30  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  02:26:58  Show Profile  Reply with Quote
So how is the result like ?


KH

Go to Top of Page

vsureshbabu
Starting Member

India
10 Posts

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

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 02/14/2007 :  02:37:16  Show Profile  Reply with Quote
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

India
10 Posts

Posted - 02/14/2007 :  02:45:03  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  02:50:58  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  04:10:11  Show Profile  Reply with Quote
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

India
10 Posts

Posted - 02/14/2007 :  04:24:11  Show Profile  Reply with Quote
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)

Singapore
17658 Posts

Posted - 02/14/2007 :  04:34:57  Show Profile  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/14/2007 :  05:04:01  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

India
10 Posts

Posted - 02/14/2007 :  06:01:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/14/2007 :  06:11:16  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
30277 Posts

Posted - 02/14/2007 :  09:13:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000