mercredi 2 décembre 2015

SQL Ordering in a 1 - N - 1 relationship

I have a SQLite DB with two tables:

A
PK | Name

B
PK | FK1 | FK2

in table B, both FK1 and FK2 reference the PK on table A (only one is actually set up as a foreign key on the DB, but the values in FK2 are still taken from the set of all PK's for table A).

The purpose of table B is to allow for non-linear orderings. This holds 'next' relationships. For example:

A
PK | Name

1 | First
2 | Second
3 | Third

B
PK | FK1 | FK2

1 | 1 | 2
2 | 2 | 3

This represents a linear flow from First -> Second -> Third.

I am looking for the best way to order the results from A. Optimally, linear flows would be generated in the order specified by table B. Non-linear flows would be slightly different, but for the most part should be ordered the same way (except for parallel paths being placed next to each other).

I've fooled around with every join I can think of, and SQLite does not support PL/SQL which is making this problem significantly more difficult. Currently, I'm simply running:

A left join B on A.PK = B.FK1 order by B.FK2.  

Whats the optimal way to join these tables and order the results?

Aucun commentaire:

Enregistrer un commentaire