Stuck on SQL statement - 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)

Stuck on SQL statement

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-06-25 : 10:55:34

I want to select several columns from a table but only where an Id matches a column in a subquery. Here is what I am trying right now. Eventually these records will end up in a table and I want all of the columns from both selects to be populated. Problem is I need to compare GroupId to ID_Eclipse and only include records where condition of inner select is met.

I have thought about creating a table from inner select and then updating each column on the outer select where the two ID fields match but this seems like it should be a simple query.

SELECT GroupId, Address, City FROM tblCustomer WHERE EXISTS(SELECT ID_Eclipse, TotalAmount FROM tblCustomer WHERE LastDate >= '01/01/03')

Thanks,
vmon

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-25 : 11:14:01

You need to use

SELECT a.GroupId, a.Address, a.City, b.totalamount FROM tblCustomer a
inner join
(
SELECT ID_Eclipse, TotalAmount FROM tblCustomer
WHERE LastDate >= '01/01/03'
) b
on a.groupid = b.id_eclipse

if you want to do it that way.

-------
Moo.

Edited by - mr_mist on 06/25/2003 11:15:19

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-06-25 : 12:35:41

Or....

 
SELECT  GroupId, Address, City 
FROM    tblCustomer c
WHERE   EXISTS (SELECT  1
                FROM    tblCustomer sub
                WHERE   c.groupid = sub.id_eclipse AND
                        LastDate >= '01/01/03')

Jeff Banschbach, MCDBA

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-25 : 13:22:55

If you want both sets then (always use yyyymmdd for dates).

SELECT c.GroupId, c.Address, c.City
FROM tblCustomer c, tblCustomer c2
WHERE c.groupid = sub.id_eclipse
AND c2.LastDate >= '20030101'
union
SELECT c.GroupId, c.Address, c.City
FROM tblCustomer c
WHERE c2.LastDate >= '20030101'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources