I am getting duplicate rows back from a select distinct statement of the
form:
SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT lots of good stuff) t0
LEFT OUTER JOIN another_table t1 ON relevant_stuff
WHERE (lots of conditions)
After re-reading the relevant pat ofVol 1 of the SQL Reference I am
unablee to see how this is possible.
For the interested (or the terminally bored) the statement in all its
gory details is attached to prevent anyones mailer from mseeing with the
line wrapping.
SELECT DISTINCT 'jhough', '000111', t0.bhid
FROM (SELECT tal.namex AS locname, tac.color_desc AS color, tad.disposal_desc AS disposal, ta.tag2 AS tag,
ta.old_tag2 AS old_tag, tc.namex AS sire_name, tc.tattoo AS sire_tattoo, tc.assoc AS sire_assoc,
tc.prefix AS sire_prefix, tc.regnum AS sire_regnum, tce.herd_id AS sire_id, tcl.namex AS sire_locnam,
td.namex AS dam_name, td.tattoo AS dam_tattoo, td.assoc AS dam_assoc, td.prefix AS dam_prefix,
td.regnum AS dam_regnum, tde.herd_id AS dam_id, tdl.namex AS dam_locnam, mgs.namex AS mgs_name,
mgs.tattoo AS mgs_tattoo, mgs.assoc AS mgs_assoc, mgs.prefix AS mgs_prefix, mgs.regnum AS mgs_regnum,
mgse.herd_id AS mgs_id, mgsl.namex AS mgs_locnam, te.namex AS recip_name, te.tattoo AS recip_tattoo,
te.assoc AS recip_assoc, te.prefix AS recip_prefix, te.regnum AS recip_regnum,
tee.herd_id AS recip_id, tel.namex AS recip_locnam, ta.controller, ta.tattoo, ta.tattoo_loc,
ta.assoc, ta.prefix, ta.regnum, ta.sex, ta.birth_date, ta.namex, ta.activex, ta.category,
ta.electronic_id, ta.commentx notes, ta.disposal_date, ta.hps, ta.mating, ta.breed_1, ta.pct_1,
ta.breed_2, ta.pct_2, ta.breed_3, ta.pct_3, ta.breed_4, ta.pct_4, ta.Assoc_Pct, ta.blood_typed,
ta.blood_case_numb, ta.dna_tested, ta.dna_case_numb, ta.bangs_id, ta.recipient, ta.donor,
ta.commercial,
CASE
WHEN emb.bhid IS NULL AND
imp.bhid IS NULL AND
(ta.mating <> 'E' or ta.mating IS NULL) AND
(birth.embryo_transfer <> 'Y' or birth.embryo_transfer IS NULL) THEN 'N'
WHEN ta.birth_date IS NOT NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'W'
WHEN ta.birth_date IS NULL AND
(emb.bhid IS NOT NULL OR
imp.bhid IS NOT NULL OR
ta.mating = 'E' OR
birth.embryo_transfer = 'Y') THEN 'Y'
END AS embryo, ta.sire_code, ta.breedcode, ta.new_date, ta.new_user, ta.bhid, mgs.bhid AS mgs_bhid,
ta.location, ta.sire_bhid, ta.dam_bhid, ta.recip_bhid, ta.color AS color_code, ta.bh_disposal_code,
DAYS(CURRENT_DATE) - DAYS(ta.birth_date) AS age_days,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/30.4375), 2) AS DECIMAL(8,2)) AS age_mos,
CAST(ROUND(((DAYS(CURRENT_DATE) - DAYS(ta.birth_date))/365.25 ), 2) AS DECIMAL(8,2)) AS age_yrs
FROM is3.animals ta
LEFT OUTER JOIN is3.color_defn tac ON ta.color=tac.bh_color_code
LEFT OUTER JOIN is3.disposal_defn tad ON ta.bh_disposal_code=tad.bh_disposal_code
LEFT OUTER JOIN is3.locations tal ON ta.location=tal.loc_id
JOIN is3.animals tc ON tc.bhid=ta.sire_bhid
LEFT OUTER JOIN is3.locations tcl ON tc.location=tcl.loc_id
LEFT OUTER JOIN is3.animals_priv tce ON tce.herd_owner_id=1 AND tce.bhid=tc.bhid
JOIN is3.animals td ON td.bhid=ta.dam_bhid
LEFT OUTER JOIN is3.locations tdl ON td.location=tdl.loc_id
LEFT OUTER JOIN is3.animals_priv tde ON tde.herd_owner_id=1 AND tde.bhid=td.bhid
JOIN is3.animals te ON te.bhid=ta.recip_bhid
LEFT OUTER JOIN is3.locations tel ON te.location=tel.loc_id
LEFT OUTER JOIN is3.animals_priv tee ON tee.herd_owner_id=1 AND tee.bhid=te.bhid
JOIN is3.animals mgs ON mgs.bhid=td.sire_bhid
LEFT OUTER JOIN is3.locations mgsl ON mgs.location=mgsl.loc_id
LEFT OUTER JOIN is3.animals_priv mgse ON mgse.herd_owner_id=1 AND mgse.bhid=mgs.bhid
LEFT OUTER JOIN is3.embryos emb on emb.bhid=ta.bhid
LEFT OUTER JOIN is3.et_implants imp on imp.bhid=ta.bhid
LEFT OUTER JOIN is3.birth birth on birth.bhid=ta.bhid ) t0
LEFT OUTER JOIN is3.animals_priv t1 ON t1.herd_owner_id=1 AND t1.bhid=t0.bhid
WHERE ((t0.location IN (SELECT loc_id FROM is3.locations
WHERE leftx BETWEEN (SELECT leftx FROM is3.locations WHERE loc_id=111111111) AND
(SELECT rightx FROM is3.locations WHERE loc_id=111111111))))
AND coalesce(t0.activex,'')<>'N'
AND (t0.controller=0 OR t0.controller=1)
AND (lower(t0.MGS_ID)<>'y' OR lower(t1.HERD_SIRE)='y')
