Clojure join multiple table

635 Views Asked by At

I am trying to solve the query in clojure. It has to return only one row. But it is returning multiple row. The function is picking the correct row from Customer table, but along with that it is fetching all the 3 row from queue table and all 4 row from service table. So totally I am getting 12 (1X3X4) row instead of only one. Any suggestion would be helpful. Thanks.

SELECT a.first_name, b.queue_name, c.service_name 
from customer a
  JOIN queue b
    on a.queue_id = b.queue_id
  JOIN service c
    on a.service_id = c.service_id
where a.empl_id = 'BA123';

(defn query-using-map
  "Generates a query using a honey sql map and returns the results"
  [query-map]
  (jdbc/with-db-transaction [conn {:datasource config/datasource}]
   (let [sql-query (sql/format query-map)
          query-result (jdbc/query conn sql-query)]
       query-result)))

(defn get-servicename-queuename [emplid]
 (jdbc/with-db-transaction [conn {:datasource config/datasource}]
 (let [result (query-using-map 
   {:select [:a.first_name :b.queue_name :c.service_name]
    :from [[:customer :a] [:queue :b] [:service :c]]
    :join [":a.queue_id=:b.queue_id and :a.service_id=:c.service_id"]
    ; OR :join [[:= :a.queue_id :b.queue_id] [:= :a.service_id :c.service_id]]
    :where [:= :a.empl_id emplid]})](println result))))

;(get-servicename-queuename "BA123")
0

There are 0 best solutions below