Hi all
I have this table


guid      datecreated    account      dateremoved
5DC40536         1.8.2006  9574DAAD    30.8.2006
A89B5B69           17.7.2006 FD20B38C      17.7.2006
2B9FD838           30.5.2006    BEB49691      14.9.2006
6C2B8DFA       17.7.2006    FD20B38C  17.7.2006
C0BAB1A4       17.7.2006    FD20B38C      17.7.2006
0CD9AF9D       17.7.2006     FD20B38C      17.7.2006
4B1A315E           30.8.2006     9574DAAD  30.8.2006
64379E19           30.8.2006     9574DAAD  null
91A5CD4C       14.9.2006     BEB49691      14.9.2006
9B70851D           14.9.2006     BEB49691      14.9.2006

The data I want is the accounts which has not a null in dateremoved,
that means, just the accounts that has an entry in the dateremoved
field ( here accounts FD20B38C and BEB49691)
I'm using this
SELECT     account
FROM         packages
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          packages
                            WHERE      (dateremoved IS NULL)))

And this does not do anything for me...

Can any one of you geniuses help me with this?
Thanks

Well you could try this approach

SELECT     account
kollatjo***@gmail.com wrote:

Show quote

Hide quote

You're missing a correlation between the main and the subquery:

E.g.:

SELECT     account
FROM         packages
WHERE     (NOT EXISTS
                          (SELECT     *
                            FROM          packages subQuery
                            WHERE      (subQuery.dateremoved IS NULL)
                                            and (subQuery.guid =
packages.guid)
                ))


ML

---
http://milambda.blogspot.com/

Perhaps I'm missing something (and therefore not qualifying as a
genius) but I think you want:

select distinct account
from packages
where account not in (select accout from packages where dateremoved is
null)


kollatjo***@gmail.com wrote:

Show quote

Hide quote

You forgot to correlate the subquery.




USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE dbo.packages
(
    guid CHAR(8),
    datecreated SMALLDATETIME,
    account CHAR(8),
    dateremoved SMALLDATETIME
);
GO

INSERT dbo.packages
SELECT
    '5DC40536', '20060801', '9574DAAD', '20060830'
    UNION ALL SELECT 'A89B5B69', '20060717', 'FD20B38C', '20060717'
    UNION ALL SELECT '2B9FD838', '20060530', 'BEB49691', '20060914'
    UNION ALL SELECT '6C2B8DFA', '20060717', 'FD20B38C', '20060717'
    UNION ALL SELECT 'C0BAB1A4', '20060717', 'FD20B38C', '20060717'
    UNION ALL SELECT '0CD9AF9D', '20060717', 'FD20B38C', '20060717'
    UNION ALL SELECT '4B1A315E', '20060830', '9574DAAD', '20060830'
    UNION ALL SELECT '64379E19', '20060830', '9574DAAD', NULL
    UNION ALL SELECT '91A5CD4C', '20060914', 'BEB49691', '20060914'
    UNION ALL SELECT '9B70851D', '20060914', 'BEB49691', '20060914';
GO

SELECT account
FROM dbo.packages p
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.packages
    WHERE account = p.account
    AND dateremoved IS NULL
)
GROUP BY account;
GO

DROP TABLE dbo.Packages;
GO




Please don't use proprietary date formats like d.m.yyyy.  Much safer to use
yyyymmdd.  Then someone who is trying to reconstruct INSERT statements so
they can build your insufficient DDL and sample data aren't scratching their
heads wondering if that's January 8th or August 1st.




<kollatjo***@gmail.com> wrote in message

Show quote

Hide quote

UNDO
XP_SENDMAIL Overload in SQL 2005
Bulk Insert To Temp Table - Security Issue
UPDATE TOP 1
Time recording query