Dracoro

Not sure what kcname is??

Anyway, it can be done two ways (if I've interpreted your requirement correctly!!!)

SELECT p.petname, d.petname, s.petname
FROM Dog p, Dog d, Dog s
WHERE p.Dam = d.DogID
AND p.Sire = s.DogID
WHERE p.petname = 'Harry'

SELECT p.petname, d.petname, s.petname
FROM Dog p
INNER JOIN Dog d ON d.DogID = p.Dam
INNER JOIN Dog s ON s.DogID = p.Sire
WHERE p.petname = 'Harry'

If you want to bring back dogs that may or may NOT have dams or sires then make the joins outer ones (OUTER JOIN or (+) syntax). Above will only bring back Harry if he has both a sire and a dam.

The 'WHERE' bit is optional (with it for just Harry's mum & dad) or leave it out to get mum & dads for all records. I've just put it in so it looks more obvious what I'm doing.

HTH.

[Edited by Dracoro - 10/7/2003 7:28:39 PM]

ScoobyWon't

ANy SQL experts want to take a look at this and give me a hint as to how to create the correct syntax. In theory I believe this is a one->many relationship.

(Excuse my attempt at ASCII art)

+-------+
¦Dogs ¦
+-------+
¦DogID ¦
¦Kcname ¦
¦Petname¦
¦Dam ¦
¦Sire ¦
+-------+

I am attempting to output the following:

"Hello my name is <%Petname%> my mother is <%Dam%> and my father is <%Sire%>."

Therefore I need to join the value of the Dam and of the Sire to it's own DogID to give me (Below are example records):

"Hello my name is Harry, my mother is Sally and my father is Barry."

Example records:

DogID: 1
Petname: Harry
Dam: 2
Sire: 3

DogID: 2
Petname: Sally
Dam:
Sire:

DogID: 3
Petname: Barry
Dam:
Sire:

How do I go about creating this query? Am I correct in thinking that I may have to do a Join dog.petname as pup and dog.sire as family.dam. Utterly confused... Help! Please!!!!

Hobo_Jojo

give up. simple.

ScoobyWon't

Excellent. Cheers. Got it working by passing the DogID in the end, hadn't thought of the outerjoin for no parents, thanks

Have it working with the following (expanded the criteria to include extra info)

SELECT Dogs.Kcname, Dogs_dam.Kcname, Dogs_sire.Kcname, Dogs.Petname, Breed.Breed, Sex.Sex
FROM ((Dogs AS Dogs_sire INNER JOIN (Dogs INNER JOIN Dogs AS Dogs_dam ON Dogs.Dam = Dogs_dam.DogID) ON Dogs_sire.DogID = Dogs.Sire) INNER JOIN Breed ON Dogs.BreedID = Breed.BreedID) INNER JOIN Sex ON Dogs.SexID = Sex.SexID
WHERE (((Dogs.DogID)=1));

Dracoro

Hope you don't take offence if I give some constructive criticism (BTW - are you doing the SQL for college course or work?) which may help you in future.
I think you're nesting too much which will have performance issues on large databases (I work with a data warehouse where this is an issue ). All the bracketing doesn't aid readability either especially if you have a high number of joins (imagine if you had to add another 7 or 8 tables!.

Try the below (as you can see, it's pretty easy to add or remove joins without worrying about where your brackets are or how nested it is)

SELECT dog.kcname, dam.kcname, sire.kcname, dog.petname, breed.breed, sex.sex
FROM Dog dog
INNER JOIN Dog dam ON d.DogID = dog.Dam
INNER JOIN Dog sire ON s.DogID = dog.sire
INNER JOIN Breed breed ON b.BreedID = dog.BreedID
INNER JOIN Sex sex ON g.SexID = dog.SexID
WHERE dog.DogID = 1;

HTH

ScoobyWon't

I'm attempting to teach myself ColdFusion/SQL in one go! Brackets are courtesy of Access generating the sql.

Have noticed my way is just returning the same name for pup, father and mother when queried through a web page through the server but when queried in Accessit provides the correct info. Bugger

I shall give it a go as you've suggested and see if it speeds up performance/works!.


[Edited by ScoobyWon't - 10/8/2003 10:57:24 AM]

[Edited by ScoobyWon't - 10/8/2003 11:00:28 AM]