sql subquery - 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

Transact-SQL (2000)

sql subquery

Author

Topic

Renierb78
Starting Member

2 Posts

Posted - 2005-06-17 : 03:43:50

I have three tables, employees(which have all the employees records, strongestcardiepersonal(which is a new table where users register to do something - so not all employees will be in here, and strongestcardiescore(which store the employees score)

EMPLOYEES:
firstname varchar(200),
surname varchar(200),
empno int4,
department varchar(100)

strongestcardiepersonal:
qpid serial NOT NULL,
title varchar(50),
firstname varchar(100),
surname varchar(100),
email varchar(100),
empno varchar(100),
department varchar(200),
timesubmitted varchar(100),
datesubmitted date,
refno varchar(400),
CONSTRAINT qpid_pkey PRIMARY KEY (qpid)

strongestcardiescore:
qsid serial NOT NULL,
score_50_total varchar(100),
score_30_total varchar(100),
score_20_total varchar(100),
timesubmitted varchar(100),
datesubmitted date,
refno varchar(400) NOT NULL,
CONSTRAINT qsid_pkey PRIMARY KEY (qsid)

What I am trying to do is to see which employees that are in the employee table, isnt in the strongestcardiescore. for this is need to do a simple subquery, but it gives an error >

select e.firstname, e.surname, e.empno
from public.employees e where e.empno = (
select distinct p.empno
from strongestcardiepersonal p, strongestcardiescore s
where p.refno = s.refno)

ERROR: more than one row returned by a subquery used as an expression

help please.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-17 : 03:53:24

Try this


select e.firstname, e.surname, e.empno 
from public.employees e where e.empno in (
select distinct p.empno
from strongestcardiepersonal p, strongestcardiescore s
where p.refno = s.refno)

Madhivanan

Failing to plan is Planning to fail

Renierb78
Starting Member

2 Posts

Posted - 2005-06-17 : 03:59:18

Thank you so much, perfect!!

quote:
Originally posted by madhivanan

Try this

select e.firstname, e.surname, e.empno
from public.employees e where e.empno in (
select distinct p.empno
from strongestcardiepersonal p, strongestcardiescore s
where p.refno = s.refno)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources