I have a table which stores a list of locations that a person can have. In my application I need to get this list of
locations,but I don't want the list to contain any null values, a person must have at least 1 location (done in
applicationcode) but the rest of them can be null, only when I bring up the list of possible locations for a person I
onlywant the not null ones, is there any way of achieving this in sql, or will I need to do this at the application
level? Normally I would just say where column is not null, but there are 11 columns in this table that can be not null.
This is the table structure:
shaz=> \d forest_person
Table "forest_person"
Attribute | Type | Modifier
------------+-----------------------+----------
person_id | integer | not null
location1 | character varying(30) |
location2 | character varying(30) |
location3 | character varying(30) |
location4 | character varying(30) |
location5 | character varying(30) |
location6 | character varying(30) |
location7 | character varying(30) |
location8 | character varying(30) |
location9 | character varying(30) |
location10 | character varying(30) |
location11 | character varying(30) |
location12 | character varying(30) |
Index: forest_person_pkey
Here is the person table:
shaz=> \d person
Table "person"
Attribute | Type | Modifier
------------------+-----------------------+----------
person_id | integer | not null
firstname | character varying(25) | not null
lastname | character varying(25) | not null
dob | date | not null
street | character varying(50) | not null
suburb | character varying(50) |
city | character varying(50) | not null
homephone | character varying(15) |
workphone | character varying(15) |
mobile | character varying(15) |
type | character varying(30) | not null
date_approved | date | not null
approved_by | character varying(50) | not null
vehicle_type | character varying(50) |
vehicle_rego | character varying(6) |
drivers_licence | character varying(10) |
firearms_licence | character varying(20) |
notes | character varying(80) |
status | character varying(10) |
Indices: firstname_idx,
fullname_idx,
lastname_idx,
person_drivers_licence_key,
person_firearms_licence_key,
person_pkey
Regards,
Sharon Cowling
