Re: Matching on more than one item?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 20:20:06 +0100

Assuming the primary key is (customer_no, query_no, date_logged, code):

SELECT customer_no, query_no, date_logged
 FROM Product
 WHERE code IN (31,32,44)
 GROUP BY customer_no, query_no, date_logged
 HAVING COUNT(*)=3

If the key is something different:

SELECT customer_no, query_no, date_logged
 FROM Product
 WHERE code IN (31,32,44)
 GROUP BY customer_no, query_no, date_logged
 HAVING COUNT(DISTINCT code)=3

Please include DDL with future posts so that we don't have to guess at keys,
etc. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Need a little help please
    ... Every table should have a primary key. ... A lack of indexes and constraints doesn't affect the insert per se. ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Update every other record with a cursor
    ... Please include DDL with future posts so that we don't have to guess at your ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: show f1 when f2 is not distinct
    ... It does have a primary key, ... "David Portas" wrote: ... > Notice that this ignores NULLs in F2. ... > SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Get the Primary Key of a table
    ... Your query returns ... the nameof the primary key columnnot the name of the key. ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Generate Random Primary Key
    ... column by making it a BIGINT or NUMERIC. ... If the column has a Primary Key ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)