Simple SQL 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 2000 Forums

SQL Server Development (2000)

Simple SQL Query

Author

Topic

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-22 : 01:37:03

Hi,

I have a simple query which run successfully in Oracle but the same query is giving problem in Sql Server.

Can u please suggest me how to write the same query in Sql Server.

Select * from employeemaster
where (empno,emplastname) = (1,'john')

Select * from employeemaster
where (empno,empdept) IN (select empno,dept from empdept)

this query will be satisfied only when the two field values are same.

Thanks in Advance

Anwer

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-22 : 02:50:17

it seems empno and emplastname are two columns of your table employeemaster.

select *
from employeemaster
where empno = 1 AND emplastname = 'john'
or
select *
from employeemaster
where empno = 1 OR emplastname = 'john'

mdanwerali
Starting Member

30 Posts

Posted - 2002-11-22 : 05:22:12

Hi,

The solution which u have mentioned in your query is for the single value checking, but what if i have more than one column to be satisfied and more than one row? as i mentioned in my earlier query.

quote:

it seems empno and emplastname are two columns of your table employeemaster.

select *
from employeemaster
where empno = 1 AND emplastname = 'john'
or
select *
from employeemaster
where empno = 1 OR emplastname = 'john'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-11-22 : 07:00:47

The Oracle syntax you originally used is not valid in SQL Server. burbakei's solutions will work in either product, however.

If you have multiple rows to match, you can use a JOIN:

SELECT E.* FROM employeemaster E
INNER JOIN (SELECT empno, dept FROM empdept) D
ON E.empno=D.empno AND E.empdept=D.empdept

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources