0

Can anyone help me out with writing the sql code for this particular output please:

List trees that are co-located(i.e tr_loc values are identical) and where one tree is the granddaughter of the other.Table data

This is what I came up with but Im sure it is wrong : 

SELECT tr_loc a, tr_loc b,  Tr_numb, Tr_forest 
FROM Tree
WHERE a.tr_loc = b.tr_loc 
  AND a.TR_parent = b.Tr_numb;
SqlZim
  • 37,248
  • 6
  • 41
  • 59
user3473184
  • 23
  • 1
  • 6
  • Please read http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557 and the accepted answer –  May 23 '17 at 20:09

1 Answers1

1

I think this is what you were going for with your query:

SELECT
a.tr_numb AS granddaughter
,c.tr_numb AS grandfather
,a.tr_loc
,a.Tr_forest

FROM
Tree a, Tree b, Tree c

WHERE
a.tr_parent = b.tr_numb
AND b.tr_parent = c.tr_numb
AND a.tr_forest = c.tr_forest
AND a.tr_loc = c.tr_loc

Notice how a, b, and c are aliases for the table Tree. That's a self join. I'll leave that query here so you can try to see where you went wrong.

However, I wouldn't recommend that syntax for doing JOINs. It's much better to learn the more explicit syntax. Here's how I would write the query:

SELECT
Tree.Tr_numb AS tree
,Parent.Tr_numb AS parent
,Grandparent.Tr_numb AS grandparent
,Tree.tr_forest
,Tree.tr_loc

FROM
Tree
INNER JOIN Tree [Parent] ON (Parent.Tr_numb = Tree.Tr_parent)
INNER JOIN Tree [Grandparent] ON (Grandparent.Tr_numb = Parent.Tr_parent)

WHERE
Tree.Tr_loc = Grandparent.Tr_loc
wwv
  • 891
  • 5
  • 14