-3

I have a table tracking which contains

id name trackingNumber
1 John Doe 1Z21343V43DF4434
2 John Doe 1Z21343V43DF4435
3 Jane Smith 1Z21343V43DF4436
4 Mark Jacobs 1Z21343V43DF4436
5 Mark Jacobs 1Z21343V43DF4437
6 Mark Jacobs 1Z21343V43DF4438

and a table orders which contains

id OrderNumber Name
1 12-12324232 John Doe
2 12-12324234 Mark Jacobs
3 12-12324236 Michael Smith

when I run the query

select * from tracking t
join orders o
on (t.name = o.name OR o.name = t.name)

I get multiple results for John Doe and Mark Jacobs since they both have a multi-piece shipment. I cannot do a limit because in real world scenario there will be over 100 unshipped orders and 100s of tracking numbers.

I want to be able to get only the latest tracking number so the order does not get updated multiple times with all of the tracking numbers as one tracking number will show them all.

SQL Fiddle of current query - http://www.sqlfiddle.com/#!9/569d4a/1/0 I only want the latest trackingnumber for each name.

rbur0425
  • 479
  • 8
  • 26
  • How do you know which one is the latest? By date? If yes, table `tracking` should have a date column but looking at your example, it does not. – FanoFN Sep 01 '21 at 01:00
  • @FaNo_FN yes there is a date column – rbur0425 Sep 01 '21 at 01:20
  • Awesome! Can you please update your question and include the date column on your related table? Also, please illustrate the expected result into your question as well. Thanks – FanoFN Sep 01 '21 at 01:32
  • @FaNo_FN it does not really change anything. The tracking table has a created datetime and the orders table has a paid_time datetime. I check that created >= paid_time to ensure the tracking was created after the order was placed. – rbur0425 Sep 01 '21 at 01:40
  • Let me ask you, for each order number will it have only 1 tracking number or more? And assuming that the tracking number is only created after it's paid, how do you link the order table with the tracking table? – FanoFN Sep 01 '21 at 01:59
  • Some orders may have only 1 tracking number and other orders may have 3-5. I only want one for each order. It does not matter which one it is. – rbur0425 Sep 01 '21 at 02:14
  • In your question you want the "latest" tracking number but your previous comment is contradicting it. Anyway, I probably understand what you're trying to do but I need to know your mysql version. – FanoFN Sep 01 '21 at 02:28
  • Using name to link two tables should be avoided. it is very likely that there will be two records with same name referring to two different persons. Next time try using say ID – Ken Lee Sep 01 '21 at 02:52
  • @FaNo_FN MySQL version 5.7 – rbur0425 Sep 01 '21 at 12:14
  • @KenLee I cant use id because the data is coming from two different sources. – rbur0425 Sep 01 '21 at 15:14
  • Is this something close to your requirement? https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5e1d7fbb3631cef0b830d09e84e16ac9 .. try run the code a few times and you should see the `trackingNumber` changes on every execute. – FanoFN Sep 02 '21 at 01:23

2 Answers2

-1

If I understand your question correctly, you only want the first result from your first table? You could search for the min(id) to recieve the record with id 1 as described here. However the same can be achieved by just sorting by ID DESC and then Limiting to 1.

If your condition should apply to the table you join, this might actually be what you want. Also on a side note, your join condition seem to be redundant, if t.name = o.name, why wouldn't o.name also equal t.name?

You only want the first result based on what order? I would suggest sorting first. Also you could only pull the trackingNumber if that's what you mean:

SELECT trackingNumber from tracking t
join orders o
on (t.name = o.name OR o.name = t.name)

Update 1

I believe I understand your issue, however it is beyond my sql capabilities to provide you with an answer that is only based on a query. Might I suggest using a bit of code to solve this? Here's how I'd do it:

  1. Run a query to get all users (select name from tracking)

  2. Then get the most recent shipping for that user

    SELECT id FROM orders WHERE name = {name} ORDER BY id DESC LIMIT 1

You could also solve this by using a query sequence, if it must be done with sql only, however this is quite complicated as demonstrated here.

lum_dev
  • 1
  • 1
-1

You only want the first result based on what order? I would suggest sorting first. Also you could only pull the trackingNumber if that's what you mean:

SELECT trackingNumber from tracking t
join orders o
on (t.name = o.name OR o.name = t.name)
Horus
  • 11
  • 3