CODEWITHSUNDEEP
androidsqlsqlite
dutt
dutt

Reputation: 8209

Add default value for unknown

I have a list of users and a list of vaccines, I'd like to keep track of which vaccines a user has so I have a vaccine_users table as well. I want to display a list of checkboxes in a view, name of vaccine and wether a user has been vaccinated with that particular vaccine.

I'm currently using a ListActivity, which I've understand recycles views so I need to have the data in the cursor, which gives me a bit of trouble since I'd like to only keep track of who has which vaccination, not all the vaccines a user does not have.

So I'd like something like this:

userId     vaccineId     hasVaccine
1          2             0
1          2             1

I've got tables user, vaccine, vaccine_user and I can query for which vaccine a user has, but it's the hasVaccine=0 row that gives me trouble since I don't store which vaccine a user doesn't have.

EDIT: I think left outer join is on the right track, I tried this:

SELECT v.name, u._id as userId, vu._id as vaccineId FROM user u LEFT OUTER JOIN vacc_user vu ON u._id = vu.userId LEFT OUTER JOIN vaccine v ON v._id = vu.vaccineId WHERE u._id = 4;

And I think it should result in this

name        userId     vaccineId
Ebola       4          2
Hepatit A   4          null

if the user is vaccinated against Ebola but not Hep A but I just get the Ebola row.

EDIT 2: Here's the relevant tables in their current form, created for sqllite3 but will hopefully work for MySql as well.

CREATE TABLE user (_id integer primary key autoincrement, name text not null default 'unnamed');
CREATE TABLE vacc_user(_id integer primary key autoincrement, userId integer not null default 0, vaccineId integer not null default 0);
CREATE TABLE vaccine (_id integer primary key autoincrement, name text default 'unnamed vacc', text text default 'no desc');

Upvotes: 1

Views: 137

Answers (3)

Barak
Barak

Reputation: 16393

Your second issue (after the edit) is becasue as far as SQL is concerned, anything with a null in the comparison is false.

You have two options:

1) Change your query to use an OR to cover the null contingency (v._id IS NULL) so you would have:

SELECT v.name, u._id as userId, vu._id as vaccineId FROM user u LEFT OUTER JOIN vacc_user vu ON u._id = vu.userId LEFT OUTER JOIN vaccine v ON ((v._id = vu.vaccineId) OR (v._id IS NULL)) WHERE u._id = 4;

2) Add entries to your table for those vaccinations not received and use default entries (maybe "nv" for "not vaccinated") either in the SQL table definition itself or though program code, and then you should achieve the results you desire with that query.

Upvotes: 1

Ali
Ali

Reputation: 12674

If I understand your requirement properly, it seems like a simple case of using a left outer join query. Left outer join will join rows that do not have a match with an empty row (all values will be null). So you can have a query like:

SELECT * FROM user u LEFT OUTER JOIN vaccine_user vu ON u.id = vu.user_id LEFT OUTER JOIN vaccine v ON v.id = vu.vaccine_id WHERE v.id=null;

You can probably make an other join with the vaccine table to get all the vaccines a user has not had if vaccine.

Note: the query is not exact since I I'll need to have the tables and stuff to write something that is accurate but I think you get the point.

Upvotes: 1

Nir Alfasi
Nir Alfasi

Reputation: 53535

easy: you INSERT a record into vaccine_user only if the user took the vaccine (and you can omit the last column)

Upvotes: 1

Related Questions