CODEWITHSUNDEEP
sqlandroid-sqlite
ak7483
ak7483

Reputation: 187

sqlite query - select data which prefers some value

I'm trying to create an sqlite query but I'm having some problems.

Let's say that table has three columns id, foreign-id and value.

I need to select all rows with distinct foreign_id with a given value, however that value may not exist for all different foreign_ids. In which case a row where value is set to some fallback value must be selected (such row always exists) for that foreign_id.

I apologize for my english since I'm not native english speaker.

Here is an example:

Table:

id | foreign_id | value
------------------------
1  | 1          | 1
2  | 1          | 2
3  | 1          | 3
4  | 2          | 1
5  | 2          | 3

If desired value is 2 and fallback value is 1 then the query should return

id | foreign_id | value|
------------------------
2  | 1          | 2
4  | 2          | 1

It return row with id 1 because it has desired value 2 for foreign_id 1. And it return row with id 4 because for foreign_id 2 a row with value of 2 does not exits, so it selects a row with fallback value of 1.

Hope that clears up my question a bit.

Upvotes: 1

Views: 300

Answers (2)

ak7483
ak7483

Reputation: 187

This is the solution that I produced based on Seth's answer.

SELECT DISTINCT (foreign_id), value, id FROM testTable 
  WHERE value = 2
UNION SELECT DISTINCT (foreign_id), value, id FROM testTable
  WHERE value = 1 
  AND foreign_id NOT IN 
  ( 
    SELECT foreign_id 
    FROM testTable
    WHERE value = 2
  )

Upvotes: 1

Seth McClaine
Seth McClaine

Reputation: 10040

You might be able to do it with a Union... something like:

SELECT DISTINCT (foreign_id), value
FROM TABLE 
WHERE value = 2
UNION
SELECT DISTINCT (foreign_id), '1' as value
FROM TABLE 
WHERE foreign_id NOT IN (
  SELECT DISTINCT (foreign_id), value
  FROM TABLE 
  WHERE value = 2
)

where everything that has a value 2 set value as 2 and everything else sets value as 1

(I haven't tested this query, you might have to do some tweaking)

Upvotes: 1

Related Questions