Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: SQL: "... where related records from column x are >= n"?

    Need a fairly standard SQL trick:
    I have a union query that gives a list where a group of 2-5 records are related to the same main key (field/column1), the second key and a general date field is what sorts the records within the main key number. Example

    1, 20.01.2004 11:53, 5032, etc.
    1, 20.01.2004 13:05, 5033, etc
    1, 02.02.2004 09:38, 7389, etc
    2, 16.01.2004 14.11, 5158, etc.
    2, 23.01.2004 16:00, 5267, etc.

    colunm 1 and 3 are the main keys.

    I want to make a query that takes this as a source, and where I can specify that I want only those main keys that have more than x sub records. In the example above , if I want those with 3 or more sub records, it should only return (the same type of listing) the 3 records for main key number 1, but not number 2, as it only contain 2 related subrecords.

    I am not sure how to specify that in a query.
    "... where related records from colunm 3 are >= 3"... (within the same main id/key in column 1)

    D.
    Last edited by kedaniel; 06-28-04 at 03:41.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select *
    from tableA ta
    INNER JOIN
    (select key, count(*) As COUNT
    from tableA
    group by key
    having count(*) > X) V ON
    ta.key = V.key
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •