Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    24

    A joining table situation

    Here is my situation:

    table A
    id int primary key
    ...

    table A1
    id int reference A on delete cascade,
    code char(2),
    ...
    primary key (id, code)

    where multiple entries with the same id but different code may exist in table A1.

    Now, is possible to have only one item per id with a selection statement something like the followings:

    select a.id, ... from A a join A1 a1 using (id) where code in ('en', 'es')

    And ideally, returning the entry with the code 'en' if there is one.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select A.id, ...
         , min(A1.code)
      from A 
    inner  
      join A1
        on A.id = A1.id
     where A1.code in ('en', 'es')
    group
        by A.id, ...
    you must group by every "..." column in the SELECT list

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Posts
    24
    You are quick, Rudy.

    The query works if the select columns only from the table A. Unfortunately, I have some columns from the A1. What I try to do is to eliminate duplicate returned result (in terms of same ID) with a bias to one code value.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you shoulda said so to start with
    Code:
    select A.id
         , FOO.BAR
         , FOO.code
      from A 
    inner  
      join A1 as FOO
        on A.id = FOO.id
     where FOO.code =
           ( select min(code)
               from A1
              where id = FOO.id
                and code in ('en', 'es')
           )
    rudy

  5. #5
    Join Date
    Jul 2003
    Posts
    24
    Sorry, I didn't know what would be involved.

    It works. I only provide an example data of the code. Say the code 'en' is the one I preferred, but it is not necessary the smallest one in any given list of codes. Any further enhacnement possibility?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, but it gets messier and messier

Posting Permissions

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