A joining table situation

Vernon · #1 (**permalink**) 12-26-03, 18:50

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.

r937 · #2 (**permalink**) 12-26-03, 19:02

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/

Vernon · #3 (**permalink**) 12-26-03, 19:37

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.

r937 · #4 (**permalink**) 12-26-03, 19:44

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

Vernon · #5 (**permalink**) 12-26-03, 20:28

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?

r937 · #6 (**permalink**) 12-26-03, 20:46

yes, but it gets messier and messier

Internet Services

Web Development

Digital Marketing

Consumer Tech