Results 1 to 6 of 6

Thread: SQL Query Help

  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: SQL Query Help

    Hello,
    I am trying to retrieve distinct ClientID's that match more than 1 item for example my query is currently

    Code:
    Select distinct ClientID from ClientRegs where RegionID = 1 and RegionID = 28
    The Table structure example is

    ClientID RegionID
    222 1
    222 28
    444 1
    444 44

    So I want the above query to return only 222 because it is the only client id that has a record for RegionID 1 and 28.

    The above query doesn't work because the RegionID column will never be both 1 and 28 on the same record.

    I've also tried
    Code:
    Select distinct ClientID from ClientRegs where RegionID = 1 or RegionID = 28
    This query does not work because it will return 444 and 222.

    Does anyone know if what I'm trying to do is possible and how it would be done?

    Thanks,
    Brian

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink Qeury

    Why not just run id dstinct by the clientID and sort it.

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Query

    How bout
    Select distinct ClientID from ClientRegs where RegionID => 1 .and. RegionID = 28 0

  4. #4
    Join Date
    Aug 2004
    Posts
    3
    I used
    Select distinct ClientID from ClientRegs where RegionID >= 1 and RegionID = 28

    That still returns all ClientID's that have either a 1 or a 28 not ClientID's that have records for both 1 and 28

  5. #5
    Join Date
    Aug 2004
    Posts
    5

    Smile use a sub query

    ---if there are fewer records
    Select distinct ClientID from ClientRegs where RegionID = 1 and ClientID in
    (select ClientID from ClientRegs where RegionID = 28)

    or
    ----if there are more records
    Select distinct a.ClientID
    from ClientRegs a , ClientRegs b
    where a.ClientID = b.ClientID
    and a.RegionID <> b.RegionID
    and a.RegionID = 1
    and b.RegionID = 28

  6. #6
    Join Date
    Aug 2004
    Posts
    3
    Thanks C Nag I used your first example and
    that worked like a charm. I was starting to get worried.




Posting Permissions

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