CODEWITHSUNDEEP
sqldatabasesqlite
OogieM
OogieM

Reputation: 55

sqlite query not getting all records if 1 table has missing data

I've got a very complex database with a lot of tables in SQLite. I'm trying to design a query that will report out a lot of data from those tables and also report out those sheep who may not have a record in one or more tables.

My query is:

SELECT sheep_table.sheep_id, 
(SELECT tag_number FROM id_info_table WHERE official_id = "1" AND id_info_table.sheep_id = sheep_table.sheep_id AND  (tag_date_off IS NULL or tag_date_off = '')) AS fedtag, 
 (SELECT tag_number FROM id_info_table WHERE tag_type = "4" AND id_info_table.sheep_id = sheep_table.sheep_id AND  (tag_date_off IS NULL or tag_date_off = '')) AS farmtag,
 (SELECT tag_number FROM id_info_table WHERE tag_type = "2" AND id_info_table.sheep_id = sheep_table.sheep_id AND (tag_date_off IS NULL or tag_date_off = '') and ( id_info_table.official_id is NULL or id_info_table.official_id = 0 )) AS eidtag,
 sheep_table.sheep_name, codon171_table.codon171_alleles, sheep_ebv_table.usa_maternal_index,  sheep_ebv_table.self_replacing_carcass_index, cluster_table.cluster_name, sheep_evaluation_table.id_evaluationid,
(sheep_table.birth_type +
sheep_table.codon171 +
sheep_evaluation_table.trait_score01 +
sheep_evaluation_table.trait_score02 +
sheep_evaluation_table.trait_score03 +
sheep_evaluation_table.trait_score04 +
sheep_evaluation_table.trait_score05 +
sheep_evaluation_table.trait_score06 +
sheep_evaluation_table.trait_score07 +
sheep_evaluation_table.trait_score08 +
sheep_evaluation_table.trait_score09 +
sheep_evaluation_table.trait_score10 +
(sheep_evaluation_table.trait_score11 / 10 )) as overall_score, sheep_evaluation_table.sheep_rank, sheep_evaluation_table.number_sheep_ranked,
sheep_table.alert01,
sheep_table.birth_date, sheep_sex_table.sex_abbrev, birth_type_table.birth_type,
sire_table.sheep_name as sire_name, dam_table.sheep_name as dam_name 
FROM sheep_table 
 join codon171_table on sheep_table.codon171 = codon171_table.id_codon171id 
 join sheep_cluster_table on sheep_table.sheep_id = sheep_cluster_table.sheep_id
 join cluster_table on cluster_table.id_clusternameid = sheep_cluster_table.which_cluster
 join birth_type_table on sheep_table.birth_type = birth_type_table.id_birthtypeid 
 join sheep_sex_table on sheep_table.sex = sheep_sex_table.sex_sheepid 
 join sheep_table as sire_table on sheep_table.sire_id = sire_table.sheep_id
 join sheep_table as dam_table on sheep_table.dam_id = dam_table.sheep_id
left outer join sheep_ebv_table on sheep_table.sheep_id = sheep_ebv_table.sheep_id
left outer join sheep_evaluation_table on sheep_table.sheep_id = sheep_evaluation_table.sheep_id 
WHERE (sheep_table.remove_date IS NULL or sheep_table.remove_date is '' ) 
and (eval_date > "2014-10-03%" and eval_date < "2014-11%")
and sheep_ebv_table.ebv_date = "2014-11-01"
order by sheep_sex_table.sex_abbrev asc, cluster_name asc, self_replacing_carcass_index desc, usa_maternal_index desc, overall_score desc

If a given sheep does not have a record in the evaluation table or does not have a record in the EBV table no record is returned. I need all the current animals returned with all available data on them and just leave the fields for EBVs and evaluations null if they have no data.

I'm not understanding why I'm not getting them all since none of the sheep have all 3 ID types (federal, farm and EID) so there are nulls in those fields and I was expecting nulls in the evaluation sum and ebv fields as well.

Totally lost in what to do to fix it.

Upvotes: 0

Views: 128

Answers (1)

Jim
Jim

Reputation: 3510

The problem would appear to be that you're using eval_date in the WHERE statement. I'm assuming that eval_date is in the sheep_evaluation_table, so when you use it in WHERE, it gets rid of any rows where eval_date is NULL, which it would be when you're using a LEFT OUTER JOIN and there's no matching record in sheep_evaluation_table.

Try putting the eval_date filter on the join instead, like this:

left outer join sheep_evaluation_table on sheep_table.sheep_id = sheep_evaluation_table.sheep_id 
    AND (eval_date > "2014-10-03%" and eval_date < "2014-11%")
WHERE (sheep_table.remove_date IS NULL or sheep_table.remove_date is '' )

Upvotes: 1

Related Questions