SQL select for Android AlterDialog displaying code/key values

Question

I'm ok with SQL but this one has me stumped. I'm about to give up on this but figured I'd post the question since other developers may find themselves looking for the same approach.

I'm working on an android multiselect AlertDialog. The dialog will be used to select values from key/code tables. In order to get this to work I need a query that returns a result set which has all of the key/code id's and values and another column with a boolean (or +1 and -1) indicating if the given person has that title. Here's the result set I need (using SQLite 3):

Given person.id = 1

TITLE.ID    TITLE.TITLE    PERSON_HAS_TITLE  
1           Parent         true OR +1
2           Teacher        true OR +1
3           Fireman        false OR -1
4           Policeman      false OR -1   


**PERSON**  
ID    NAME  
1     Sue  
2     Scott  
3     Jim  

**TITLE**   
ID    TITLE  
1     Parent  
2     Teacher  
3     Fireman  
4     Policeman

**PERSON_TITLE**  
PERSON_ID   TITLE_ID  
1           1  
1           2  
2           4

Any ideas?

Adriano Carneiro · Accepted Answer · 2011-08-10 18:34:59Z

1

This will do it:

SELECT title.id,
       title.title,
       ( CASE
           WHEN EXISTS(SELECT person_id
                       FROM   person_title
                       WHERE  person_id = 1
                       AND    title_id = title.id) THEN 1
           ELSE -1
         END ) as person_has_title
FROM   title

Alternatively, this will also work:

SELECT title.id,
       MAX(title.title) as title,
       MAX(CASE
             WHEN person_id IS NULL THEN -1
             ELSE 1
           END) as person_has_title
FROM   title
       LEFT JOIN person_title
         ON ( title.id = person_title.title_id and person_title.person_id = 1)
GROUP  BY title.id

edited Aug 10, 2011 at 18:34

answered Aug 10, 2011 at 16:41

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user806821 Over a year ago

wow, awesome....thanks...the second one works perfectly, the first one returns +1 for all rows...i never would have figured this out...thanks again.

Adriano Carneiro Over a year ago

There was an error on the first query, it should work as well now.

Collectives™ on Stack Overflow

SQL select for Android AlterDialog displaying code/key values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related