-1

I am using SQLite and I have a problem with making correct query request in following scenario:

I have four tables, let's call them t.a, t.b, t.c, t.d

t.c is in multiple to one relation with t.d, so t.c has column called parent_id_d

t.a and t.c are in multiple to multiple relation with each other so I created junction table called t.b to link them to each other.

Now I want to make query request that returns joined rows from t.a and t.d where each row should have distinct entries in id_a column from table t.a and id_d column from table t.d (or parent_id column from t.c, its the same), generally something like DISTINCT id_a, id_d but I want all rows from that table returned so I probably should use GROUP BY with MIN or MAX as stated in this answer DISTINCT clause in SQLite But I can't get it working

Community
  • 1
  • 1
Srokowski Maciej
  • 431
  • 5
  • 15
  • 2
    could you show some sample data, the query you have so far, the results it gives, and the results you expect – mc110 Jun 23 '14 at 08:21

1 Answers1

0

I finally managed to resolve it with this query:

SELECT * FROM t_a INNER JOIN (SELECT DISTINCT id_parent_a, id_parent_d FROM t_b INNER JOIN t_c ON id_parent_c= ic_c) ON id_a = id_parent_a INNER JOIN t_d ON id_parent_d = id_d

where:

  1. id_parent_a is column in t_b with foreign key from t_a
  2. id_parent_c is column in t_b with foreign key from t_c
  3. id_parent_d is column in t_c with foreign key from t_d
Srokowski Maciej
  • 431
  • 5
  • 15