Nested query between two tables

Question

I'm trying to write a query which combines data from two tables. Both tables contain information about certain objects. An object is uniquely defined by a triple (x,y,z) (each x, y and z have a separate column in T1) as well as by its name (the primary key in T1). In T2 the primary key is name2. There's also a column name1 which indicates the object with which object name2 is linked to, and a column w. So T1 contains the objects' names and their triples while T2 tells us which objects are linked ('linked' is not an equivalence, if object1 is linked to object2 then object2 is not linked to object 1).

We are given x, y and t and want the triples of the object that are linked to objects with x and y on their first coordinate with w>t.

I tried to write a query where we need matches object with a specific name:

SELECT 
    (SELECT x FROM T1 WHERE name=name2), 
    (SELECT y FROM T1 where name=name2) AS k, 
    w 
FROM T2 
WHERE name1=nn3 
AND w>t 
ORDER BY k;

but I can't get to how to write it when we may have more than one object from T1.

I don't have access to the database, I only know the columns of the tables so I'm having a hard time solving this without trying it out on the database. I'm confused by going from T1 to T2 back to T1 again.

Answer 1

From your question I read the table definitions to be:

T1            -- a list of objects
    x,
    y,
    z,
    name      -- primary key, hence unique

T2            -- a list of objects linked to an object in T1
    name2,    -- primary key
    name1,    -- foreign key to T1.name
    w

If we have three objects A, B and C all three will appear in T1. If B and C are linked to A, T2 will be (inter alia)

Name2  Name1
B      A
C      A

We are given x, y and t and want the triples of the object that are linked to objects with x and y on their first coordinate with w>t.

As you point out, T1 contains information about the objects and T2 about how objects are linked. To retrieve matching information from more than one table you use the JOIN syntax. Since there are two objects in a linkage you must have a separate JOIN for each. It is OK to reference a table more than once in a query. You should use aliases to clarify what role a table is fulfiling each time it is mentioned. This give something like

from T2 as linkage
inner join T1 as linked_from
    on linked_from.name = T2.name1  -- note different columns in T2
inner join T1 as linked_to
    on linked_to.name = T2.name2    -- note different columns in T2

Personally I don't like linked_from and linked_to as they are vague and generic. You should use whatever is specific and meaningful in the context of your problem.

You don't say whether the given x and y refer to the linked_from or the linked_to object. This will be very significant to the output of the query, but it is trivial to change in the SQL - just use the other alias - so I'll assume it is the parent object. The where clause is

where link_from.x = @x_given_value
and link_from.y = @y_given_value
and linkage.w > @t_given_value

You do not need to put additional sub-queries in the SELECT clause since you have already referenced all the tables and table-roles you need in the FROM cluase. So it becomes

SELECT
    link_to.x,
    link_to.y as k,
    linkage.w

I'm confident you can work out the ordering.

Be aware of the cardinalities involved in your database i.e. how many rows match each condition. You state that x, y and z together are unique. Since only x and y are supplied you may well get multiple rows returned. Similarly there may well be many rows with a value of w greater than the given value of t, even for the same name1, name2 values. There will be many rows in T2 for each row in T1.

I realise this is an example and that you have probably "simplified" the names for the question, but any time you find yourself naming columns with a numeric suffix, you're doing something wrong. Maybe you haven't normalised sufficiently, or maybe you haven't understood the relationships between the data items.