Join to same table several times in a single query

Question

I'm trying to get deeper into "advanced"-SQL'ing but have a slight problem with some pretty basic stuff. I have one table, where one row refers to another row. There are of course unique id's as well but I'll skip those here:

+----------+----------------------------+
| field    | name    |      value       |
+----------+----------------------------+
| 1        | aa      |         0        |
| 1        | ab      |         0        |
| 2        | ba      |         1        |
| 2        | bb      |         1        |
| 3        | ca      |         2        |
| 3        | cb      |         2        |
+----------+----------------------------+

What I want to accomplish is to get field when I know field=3 and name= 'ca'.

I've tried something like this:

SELECT table.value AS parent_id FROM table WHERE table.field=3 AND table.name='ca' 

That works at some point, it lists everything at 2:field, I need then to find value 1 from the field. BUT if the 2:field does not have any references (as above illustrated, 1:field) then I need the last value which will be 2:field.

How would that be possible in MySQL?

Answer 1

What you need is a self-join by using the same table TWICE in the same query, but different ALIAS...

select 
      t1.field,
      t1.name,
      t1.value as ThisIsYourParentKey,
      t2.name as ParentName,
      t2.value as GrandParentKey
   from
      YourTable t1
         left join YourTable t2
            on t1.value = t2.field
   where
      t1.name = 'a2'