I have 2 tables in mariadb
Table 1 id,did,status
Table 2 id,did,status
Table 1 can contain multiple records with the same did
So first step is I am trying to consolidate this into 1 row with the status being the highest entered id
For example if we have the following 3 rows
0, 9923323344, -1
1, 9923323344, -1
2, 9923323344, 1
Then the row I would get back is
0, 9923323344, 1
My attempt at this is
SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM Table1 WHERE carrier='2' ORDER BY id DESC) GROUP BY did
Which gives me back wrong results for a specific example I got back
0,2078009797, -1
When I was expecting
0,2078009797, 1
The second part of this is once I have a correct list I would then like to get all of the rows out of this list that have a status of -1
SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM Table1 WHERE EXISTS (SELECT * FROM Table1 WHERE carrier='2' ORDER BY id DESC) GROUP BY did) AND status='-1'
This also returns incorrect results as I get the following back for a specific example
0,2078009797, -1
1,2078009797, -1
2,2078009797, -1
When I was expecting
0,2078009797, 1
And then finally the last part of this is to check the list from the last query against Table2 and see if there is any rows with a status!='-1' AND did=Table1.did
