Select individual rows of identifiers based on the lowest value column with a combined priority

Question

Simplified table structures, all INT and non PK columns outside of identity columns:

Nodes (n) table:id

Attributes (a) table: id, node_id,type_id

Table types (t) : id,priority

I am trying to select a set of attributes, each of which has the lowest .priority type for its corresponding node. Although there are several attributes on node_id, I only want to select the one with the lowest priority value:

a1 n1 t1 p0 *
a2 n1 t2 p1 
a3 n2 t2 p1 *
a4 n2 t3 p2  

This is the main request I'm working with, and at this point I'm stuck too:

   SELECT * 
     FROM a 
LEFT JOIN t ON a.type_id = t.id 
 GROUP BY node_id

, MIN, , node_id .

Answer 1

" n--", , , (Type) (Attributes).

, (a1) Attributes, node_id .

SELECT a1.*
FROM Attributes a1 INNER JOIN Type t1 ON (a1.type_id = t1.id)
LEFT OUTER JOIN (
  (Attributes a2 INNER JOIN Type t2 ON (a2.type_id = t2.id))
  ON (a1.node_id = a2.node_id AND t1.priority > t2.priority)
WHERE a2.node_id IS NULL;

, . , , . , , ?

a1 n1 t1 p0 
a2 n1 t1 p0 
a3 n2 t2 p1 
a4 n2 t3 p1 

PS: , , " n--" . , SO, .

Answer 2

- ( ):

SELECT      n.*, a.*
FROM        Nodes n
LEFT JOIN   Attributes a
        ON  a.id = (SELECT      x.id --//TOP 1 x.id
                    FROM        Attributes x
                    INNER JOIN  Type t
                            ON  x.type_id = t.id
                    WHERE       x.node_id = n.id
                    ORDER BY    t.priority ASC,
                                --//just in case there are 2 attributes 
                                --//with the same priority, order also on x.id
                                x.id ASC
                    LIMIT 1
                    )