0

I have a MySQL database that I imported to Google Data Studio, and I have made the following query to get the data from multiple tables:

SELECT sw.waybill_number, sw.order_id as sworder_id, sw.status, sw.cod, sw.id AS sw_id, sw.created_at as sw_ca, c.shipping_price, c.id as c_id, oi.id as oi_id, oi.created_at as oi_ca, oi.order_id as oiorder_id, oi.product_id as oip_id, p.created_at as p_ca, p.id as p_id, p.name
FROM shipping_waybills sw
JOIN cities c
ON sw.state = c.name_en
LEFT JOIN order_items oi
ON sw.order_id = oi.order_id
LEFT JOIN products p
ON oi.product_id = p.id;

However, there are multiple values related to the column i want (sw.waybill_number), but I only want one value, it doesn't matter which one.

How can I do that??

Mina Boulos
  • 1
  • 1
  • 1
    Please provide sample data and desired results. Also be clear on the database you are using. Is this MySQL or Data Studio? – Gordon Linoff Jul 26 '21 at 21:39
  • Is `waybill_number` unique in the `shipping_waybills` table? – Barmar Jul 26 '21 at 21:40
  • Replace the `order_items` table with a subquery that gets the most recent row for each `order_id`. See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1 for how to do that in MySQL. – Barmar Jul 26 '21 at 21:42
  • What version of MySql? Sounds like a job for `row_number()`, but it's only available in 8.0 and later. – Joel Coehoorn Jul 26 '21 at 21:43
  • Do you mean there are multiple rows per `sw.waybill_number`? "I only want one value, it doesn't matter which one." One what? Value or row? – dougp Jul 26 '21 at 23:13
  • I want one row corresponding to sw.waybill_number, and I don't care which row. I just want one. – Mina Boulos Jul 27 '21 at 08:29

1 Answers1

0

Since you don't care which values are on each sw.waybill_number...

SELECT sw.waybill_number
, MIN(sw.order_id) as sworder_id
, MIN(sw.status) as status
, MIN(sw.cod) as cod
, MIN(sw.id) AS sw_id
, MIN(sw.created_at) as sw_ca
, MIN(c.shipping_price) as shipping_price
, MIN(c.id) as c_id
, MIN(oi.id) as oi_id
, MIN(oi.created_at) as oi_ca
, MIN(oi.order_id) as oiorder_id
, MIN(oi.product_id) as oip_id
, MIN(p.created_at) as p_ca
, MIN(p.id) as p_id
, MIN(p.name) as name

FROM shipping_waybills sw
  INNER JOIN cities c ON sw.state = c.name_en
  LEFT OUTER JOIN order_items oi ON sw.order_id = oi.order_id
  LEFT OUTER JOIN products p ON oi.product_id = p.id

GROUP BY sw.waybill_number
dougp
  • 2,810
  • 1
  • 8
  • 31
  • Hello, thanks for answering my question. But, I tried this query, and it still gives me multiple values for sw.waybill_number. How to remove those duplicates? – Mina Boulos Jul 27 '21 at 08:28
  • Provide sample structure and data. – dougp Jul 27 '21 at 22:34
  • With the `GROUP BY` each `sw.waybill_number` will appear only once in the output. You must have tried something other than what I posted. – dougp Jul 27 '21 at 22:36