CODEWITHSUNDEEP
mysql
cheesemarathon
cheesemarathon

Reputation: 69

Select data from the last complete row

I have a spreadsheet that is being migrated into a MySQL database. In the future the spreadsheet won't be filled in. Data will be uploaded directly to the database. The spreadsheet is now in two tables. One, called Piles, contains info on the product. The other, called Cubes, contains test results performed on the products. Sample data from the two tables is below:

Piles:

+----------+------------+---------+-----------------+
| ID       | Date       | MouldID | StockCode       |
+----------+------------+---------+-----------------+
| 23A42895 | 2017-06-09 | 23A     | 250CDJ09.0/0412 |
| 23B42895 | 2017-06-09 | 23B     | 250CDJ07.0/0412 |
| 642895   | 2017-06-09 | 6       | 250CDJ09.0/0412 |
| 8642895  | 2017-06-09 | 86      | 250CDJ07.0/0412 |
| 842895   | 2017-06-09 | 8       | 250CDJ09.0/0412 |
+----------+------------+---------+-----------------+

Cubes:

+---------+------------+-------------+---------+
| ID      | Date       | ConcTotalM3 | MouldID |
+---------+------------+-------------+---------+
| 2342895 | 2017-06-09 |        18.1 |      23 |
| 4842895 | 2017-06-09 |        57.9 |      48 |
|  842895 | 2017-06-09 |       108.4 |       8 |
| 1542895 | 2017-06-09 |       154.7 |      15 |
| 6242895 | 2017-06-09 |       204.6 |      62 |
+---------+------------+-------------+---------+

Now the spreadsheet was filled in by hand. When the Mould ID and Date matched the info was all written on one row. The data in the cubes table was then dragged down until another matching row was found and the process repeated.

My question is, how can i write a sql query that would output the same. I can ofc write a query that joins on date and MouldID but this won't "drag data down" Note if the numeric part of MouldID matches then that is a match for the row. A and B doesnt matter. I have included what I would expect the output to be from the sample data for reference.

+----------+------------+---------+-----------------+-------------+
| ID       | Date       | MouldID | StockCode       | ConcTotalM3 |
+----------+------------+---------+-----------------+-------------+
| 23A42895 | 2017-06-09 | 23A     | 250CDJ09.0/0412 |        18.1 |
| 23B42895 | 2017-06-09 | 23B     | 250CDJ07.0/0412 |        18.1 |
| 642895   | 2017-06-09 | 6       | 250CDJ09.0/0412 |        18.1 |
| 8642895  | 2017-06-09 | 86      | 250CDJ07.0/0412 |        18.1 |
| 842895   | 2017-06-09 | 8       | 250CDJ09.0/0412 |       108.4 |
+----------+------------+---------+-----------------+-------------+

Edit: So i have removed some columns for clarity as i think it was confusing things. I don't have a using a join with the MouldID. At the moment i am using a script which tells me the position of the letter in the string. Then i use left() to get the number part only.

What i can't do is get the join to work to use the last match if it can't match the current row. To explain my example output the first two lines are matches as there is a mould 23 in the Cubes table. Mould 6 doesn't have a match so it uses the last match (from the row above). The same occurs for mould 86. Mould 8 does have a match in the Cubes table so it uses that. I hope this clears things up a bit more. Thanks

SQL Fiddle for reference: http://sqlfiddle.com/#!9/6f19df/2

Upvotes: 1

Views: 60

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a pair of nested replace for avoid A , B and match with cubes.ID

    select 
      Date
      , a.MouldID
      , a.StockCode
      , a.Length 
      , a.Serials
      , a.PrintNum
      , b.ConcTotalM3
      , b.Quantity
      , b.Time 
      ....
    from  Piles a
    inner join Cubes b on replace(replace(a.ID, 'A', '') , 'B','') = b.id

looking to your sqlfiddle this match 

  a.Date
  , a.ID
  , b.ID
  , replace(replace(a.ID, 'A', '') , 'B','')
  , a.MouldID
  , a.StockCode
  , b.ConcTotalM3
from  Piles a
inner join Cubes b on replace(replace(a.ID, 'A', '') , 'B','') = b.ID

Upvotes: 2

Related Questions