"David Thielen" wrote:

> Hi;
>
> Ok, this should be an easy select but everything I have tried has not worked.
>
> I have 2 tables with a PK:FK relationship. So Customer.customerId =
> Purchase.customerId. I need all rows in Customer where they have zero
> purchases. So all rows in the Customer table where there is no table in
> Purchase with their customerId.
>
> Any suggestions?
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>

"Greg Larsen" wrote:

> Here is something that should work for you. This example uses the
> AdventureWorks database.
>
> select * from AdventureWorks.Sales.Customer C
> left outer join AdventureWorks.Sales.SalesOrderHeader H
> on C.CustomerID = H.CustomerID
> where H.CustomerID is null
> --
> If you are looking for SQL Server examples check out my Website at
> http://www.geocities.com/sqlserverexamples
>
>
> "David Thielen" wrote:
>
> > Hi;
> >
> > Ok, this should be an easy select but everything I have tried has not worked.
> >
> > I have 2 tables with a PK:FK relationship. So Customer.customerId =
> > Purchase.customerId. I need all rows in Customer where they have zero
> > purchases. So all rows in the Customer table where there is no table in
> > Purchase with their customerId.
> >
> > Any suggestions?
> >
> > --
> > thanks - dave
> > david_at_windward_dot_net
> > http://www.windwardreports.com
> >
> > Cubicle Wars - http://www.windwardreports.com/film.htm
> >
> >

"Steven Cheng[MSFT]" wrote:

> Hi Dave,
>
> Another simple way is first get the distinct customerID list in your FK
> table and then query the PK table and filtering those rows matches the ones
> in that customerID list from FK table. e.g.
>
> ============
> select CustomerID, AccountNumber from Sales.Customer where CustomerID not
> in (select DISTINCT CustomerID from Sales.SalesOrderHeader)
> ============
>
> Sincerely,
>
> Steven Cheng
>
> Microsoft MSDN Online Support Lead
> 
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>

"David Thielen" <thielen@nospam.nospam> wrote in message
news:1D10381F-07BD-46BD-AD2D-B6DE08380D1D@microsoft.com...
> As always brillinat - thanks. (And tell MS to transfer you to Redmond and
> put
> you on a dev team.)
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
>
>
> "Steven Cheng[MSFT]" wrote:
>
>> Hi Dave,
>>
>> Another simple way is first get the distinct customerID list in your FK
>> table and then query the PK table and filtering those rows matches the
>> ones
>> in that customerID list from FK table. e.g.
>>
>> ============
>> select CustomerID, AccountNumber from Sales.Customer where CustomerID not
>> in (select DISTINCT CustomerID from Sales.SalesOrderHeader)
>> ============
>>
>> Sincerely,
>>
>> Steven Cheng
>>
>> Microsoft MSDN Online Support Lead
>>
>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>>