Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
> >Hi all
> >I have these two tables, account and package and i want to get all
> >accounts that have more than 1 package where dateremoved is null.
> >
> >This is what I have so far..
> >
> >select accounts.name, packages.guid, packages.datecreated,
> >packages.dateremoved
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.datecreated > '2007-01-12 00:00:00'
> >and (packages.dateremoved is null)
> >order by packages.datecreated
> >
> >This gives me number of accounts and i can see the accounts that have
> >more than 1 package where the dateremoved field is null but I only want
> >to get those accounts..
> >
> >I did try this;
> >
> >select accounts.guid, packages.guid, packages.datecreated
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.dateremoved is null
> >and (packages.dateremoved in ( select packages.dateremoved
> >from packages
> >GROUP BY (packages.dateremoved)
> >HAVING count(packages.dateremoved) > 1
> >
> >but this gives me now results.. not error, just empty results.
> >
> >Can you guys and girls help me with this'
> >thanx|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:
>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.