Search code examples
javascriptsqldatabasemany-to-manyknex.js

How to get 2 fields from MySQL many-to-many relationship?

I have such tables in DB:

transaction_item
----------------
transaction_id
item_id

transactions
----------------
id
created_at

store_items
----------------
id
price

I need to get object {created_at: price}. This is 'many to many', so, transaction_item is the main table.

P.S. I wrote something like object {created_at: price} because I use knex.raw to get it.

Solution

  • You could use two joins:

    SELECT created_at, price
FROM   transactions t
JOIN   transaction_item ti ON t.id = ti.transaction_id
JOIN   stote_item i ON i.id = ti.item_id