CODEWITHSUNDEEP
sqlite
Trenton J
Trenton J

Reputation: 27

How can I properly handle an sqlite table self reference?

I have created an sqlite db for a small cattle herd.

CREATE TABLE Animals
(
animal_id PRIMARY KEY,
animal_name CHAR(15) NULL,
date_born DATE NULL,
f_parent REFERENCES Animals (animal_id) NULL,
m_parent REFERENCES Animals (animal_id) NULL,
date_purchased DATE NULL,
registered BIT NOT NULL,
gender CHAR(1) NOT NULL CHECK(gender IN ("M","F")),
breed INTEGER NOT NULL REFERENCES breed (breed_id)
);


CREATE TABLE termination (term_key INTEGER PRIMARY KEY, animal_id   INTEGER, term_date DATE, sold BIT, price SMALLMONEY, comp_market_price SMALLMONEY, comp_market_tier TEXT);

I have this statement:

SELECT a1.animal_id, a1.animal_name, a1.f_parent, t1.term_date,   t1.price, t1.comp_market_price, t1.comp_market_tier
FROM Animals AS a1, termination AS t1
WHERE a1.animal_id = t1.animal_id
AND a1.f_parent NOT NULL;

results are:

id#|'animal name'|'parent id#'|date sold ...

15|some name|4|2014-05-26 ...

...

which is correct and what I wanted except that in place of 'parent id#' I want the parent's name. The parent id# is a key in the same table as the offspring (as you see from my create statement above), but I can't figure out how to deal with this self-reference. I know the issue is rather common, and I've tried view tables, multiple joins, etc. to no avail. Please show code snippet of how I can print the same results showing the parents name in place of parent id#/key no.

thank you very much!

Upvotes: 1

Views: 1106

Answers (1)

tonypdmtr
tonypdmtr

Reputation: 3235

Something like this maybe?

select a.animal_id, a.animal_name,
       (select animal_name
          from animals
          where a.f_parent = animal_id) as parent,
       t.term_date, t.price, t.comp_market_price, t.comp_market_tier
  from animals as a, termination as t using(animal_id)
  where a.f_parent not null;

or this? (better execution plan)

select a.animal_id as id, a.animal_name as name,f.animal_name as mother,
       t.term_date, t.price, t.comp_market_price, t.comp_market_tier
  from animals as a, termination as t using(animal_id),
       animals f
  where a.f_parent = f.animal_id
    and a.f_parent is not null;

Upvotes: 1

Related Questions