How to merge two tables with one to many relationship

Question

I have two tables to main orders and ordered products.

Table 1: ORDERS
"CREATE TABLE IF NOT EXISTS ORDERS("
                       "id_order INTEGER PRIMARY KEY AUTOINCREMENT,
                       "o_date TEXT,"
                       "o_seller TEXT,"
                       "o_buyer TEXT,"
                       "o_shipping INTEGER,"
                       "d_amount INTEGER,"
                       "d_comm INTEGER,"
                       "d_netAmount INTEGER)"
Table 2: ORDERED_PRODUCTS
"CREATE TABLE IF NOT EXISTS dispatch_products("
                       "id_order INTEGER NOT NULL REFERENCES ORDERS(id_order),"
                       "product_name INTEGER,"
                       "quantity INTEGER,"
                       "rate INTEGER)"

I tried to join these two tables using following query:

SELECT *
FROM ORDERS a
INNER JOIN ORDERED_PRODUCTS b
ON a.id_order = b.id_order
WHERE a.buyer = 'abc'

The issue is with the entries with multiple products in table 2. The output I'm getting is like below:

order_ID date seller buyer Ship   amt  comm nAmt Prod  Qty Rate
1             A      x       5    100  5    115  Scale 10  10
2             B      abc     10   100  5    115  pen    5  10
2             B      abc     10   100  5    115  paper 10   5
3             C      xyz     10   100  5    220  book   5  20
3             C      xyz     10   100  5    220  stapl 10  10

expected output:
order_ID date seller buyer Ship   amt  comm nAmt Prod  Qty Rate
1             A      x       5    100  5    115  Scale 10  10
2             B      abc     10   100  5    115  pen    5  10
                                                 Paper 10   5
3             C      xyz     10   100  5    220  Book   5  20
                                                 Stapl 10  10

You did not post the output. I am assuming that the issue is with duplicate orders where the order has multiple products? If so, that will not be easy with one query. I would use two queries and build the results to maintain the relationships i.e an order can have multiple products — Phuthib, Jan 08 '19 at 08:50
@Phuthib Even if I use two queries how can I remove duplicate entries from table 1 — John, Jan 08 '19 at 08:57
@John . . . Those are not duplicate entries. SQL queries return entire tables. If you want to reformat the data, you should do that at the application level. — Gordon Linoff, Jan 08 '19 at 12:09

Caius Jard · Accepted Answer · 2019-01-08T09:44:15.573

Databases don't really work like that; you got what you asked for, and with no duplicates (all rows are different). You're looking at the columns of data that came from orders and saying "oh, the data is duplicated" but it isn't - it's joined "in context"

Imagine I gave you just one of your sample rows from your expected output:

                                             Paper 10   5

Promise I just copy pasted that.

What order is it from?

No idea.. You've lost the context, so it could be from any order. Rows are individual entities, that stand alone and without reference to any other row, as a set of data. This is why the same order info needs to appear on each row. A database could be made to produce the expected output you asked for, but it would be really quote complex in a low end database like sqlite. More important to me is to point out why there's a difference between what you thought the query would give you, and what it gave you, as I think that's the real problem: the query gave you what it was supposed to, there's no fault in it; it's more a faulty assumption of what you'd get

If you're trying to prepare a report that uses the order as some kind of header, select them individually in the front end app. Select ALL the orders, then one by one (order by order) pull all the item detail out, building the report as you go:

myorders = dbquery("SELECT * FROM ORDERS")
for each(order o in myorders)
  print(o.header)
  details = dbquery("SELECT * FROM dispatch_products where id_order = ?", o.id)
  for each(detail d in details)
    print(d.prod, d.qty, d.rate)

I knew that the data I got from query is perfectly fine, just wanted to know if its possible to get data in custom format. I guess I'll do something(handle in front end) like you suggested. Thanks! Also, you mentioned formatting could be possible with other db, could you suggest db? — John, Jan 08 '19 at 09:41
It's really a data presentation thing and hence is the front-end's responsibility, though you can abuse the database into doing it. What version of sqlite are you using? — Caius Jard, Jan 08 '19 at 09:43

score 0 · Answer 2 · answered Jan 08 '19 at 09:56

Here's a way to make the DB do it, but you'll need a version of SQLite that supports window functions (3.10 doesn't) or another db (SQLS > 2008, Oracle > 9, or other big-name db from the last 10 or so years, or a very recent MySQL):

SELECT 
  CASE WHEN rn = 1 THEN d.o_date END as o_date,
  CASE WHEN rn = 1 THEN d.o_seller END as o_seller,
  CASE WHEN rn = 1 THEN d.o_buyer END as o_buyer,
  CASE WHEN rn = 1 THEN d.o_shipping END as o_shipping,
  CASE WHEN rn = 1 THEN d.d_amount END as d_amount,
  CASE WHEN rn = 1 THEN d.d_comm END as d_comm,
  CASE WHEN rn = 1 THEN d.d_netAmount END as d_netAmount,
  d.name,
  d.qty,
  d.rate
FROM
  SELECT o.*, op.name, op.qty, op.rate, row_number() over(partition by o.id_order order by op.name, op.qty, op.rate) rn
  FROM ORDERS o
  INNER JOIN ORDERED_PRODUCTS op
  ON o.id_order = op.id_order
  WHERE o.buyer = 'abc'
) d
ORDER BY d.id_order, d.rn

We basically take your query, add on a row number that restarts every time order id changes, and only show data from the orders table where rownumber is 1. If your SQLite doesn't have row_number you can fake it: How to use ROW_NUMBER in sqlite which i'll leave as an exercise for the reader :)

Thanks!! I'll try this sometime :) – John Jan 08 '19 at 10:30 — John, Jan 08 '19 at 10:30

How to merge two tables with one to many relationship

2 Answers2