Hi,

I'm working on a database for dog breeders. Obviously, parentage is a big
issue in this area. Does ADS have anything similar to the "connect by"
clause that's used in Oracle? If not, could someone give a bit of advice on
the query I'm struggling with?

I have a table called "dogs". I won't list all of the fields since most of
them aren't relevant to this case. The important ones are:
dog_id autoinc PK
name char (40)

I also have a linking table called "parents".
dog_id int FK
sire_id int FK
dam_id int FK

All three FKs reference dogs.dog_id.

I wrote a query that brings up the dog's name and the names of the parents.
This works:
SELECT d0.name, d1.name sire, d2.name dam
from dogs d0, dogs d1, dogs d2, parents p
where d2.dog_id = p.dam_id
and d1.dog_id = p.sire_id
and d0.dog_id = p.dog_id

However, I need to do a left join, since it's not required to have the dog's
parents listed. I want to list ALL of the dogs' names, regardless of whether
the dog's parents are in the DB. This is where my problem shows up. I
attempted to do a left join from d0 to parents all by itself, and it worked
fine. However, adding the other self joins didn't work. I tried putting in
left joins for the other parts of the self join, as follows:

SELECT d0.name, d1.name sire, d2.name dam
from dogs d0 left join parents p0 on d0.dog_id=p0.dog_id,
dogs d1 left join parents p1 on d1.dog_id=p1.sire_id,
dogs d2 left join parents p2 on d2.dog_id=p2.dam_id
where p0.dog_id = p1.dog_id
and p1.dog_id = p2.dog_id

However, the record set only returns the rows where the dog's parents are in
the DB. Could anyone offer advice as to what I'm doing wrong?

Thanks,

Mike Trozzo

Sorry to follow up my own post, but I figured it out using multiple left
joins. If anyone needs to do this, here´s the query:

select d1.dog_id, d1.name dog, d2.name dad, p.sire_id, d3.name mom, p.dam_id
from (dogs d1 left join parents p on d1.dog_id = p.dog_id)
left join dogs d2 on d2.dog_id = p.sire_id
left join dogs d3 on d3.dog_id = p.dam_id



Thanks anyway!