join on multiple tables

[ariel123]

Is there a way to join on more than two tables?

[LauJensen]

Yes - Simply keep on joining

clojureql.core> (-> (join (table :t1) (table :t2) :id)
                    (join (table :t3) :id))
SELECT t1.*,t2.*,t3.* FROM t1 JOIN t2 USING(id) JOIN t3 USING(id)

[ariel123]

Oracle throws this expception when doing the above join:

ORA-25154: column part of USING clause cannot have qualifier

One way to fix it is by removing the table name qualifiers in the select part, i.e.:

SELECT * FROM t1 JOIN t2 USING(id) JOIN t3 USING(id)

But I'm not sure how to do it in clojureql library.

Another work around is using join and where query, i.e.: (-> (join (table :t1) (table :t2) (where (and (= :t1.id :t2.id))) (join (table :t3) (where (and (= :t1.id :t3.id)))))))