1

I have two tables and I want the following result: if CID and CID2 are equal, print cpu about value of CID2.

Table A:

+------+---------------------+--------------------+-----------------+
| CID  | time                | step               | time_in_seconde |
+------+---------------------+--------------------+-----------------+
|    1 | 2017-07-27 06:26:50 | gege               |          10.229 |
|    2 | 2017-07-27 06:26:58 | rere               |          10.239 |
|    3 | 2017-07-27 06:35:52 | gege               |          13.229 |
|    4 | 2017-07-27 06:36:56 | titi               |          12.823 |
|    5 | 2017-07-27 06:55:04 | fefe               |          12.667 |

Table B:

+------+---------------------+-----------------+
| CID2 | time                | cpu             |
+------+---------------------+-----------------+
|    3 | 2017-07-27 06:35:52 |          0.01   |
|    4 | 2017-07-27 06:36:56 |          0.05   |
|    5 | 2017-07-27 06:55:04 |          0.03   |

Result:

+------+---------------------+-----------------+
| CID2 | time                | cpu             |
+------+---------------------+-----------------+
|    1 | 2017-07-27 06:26:50 |                 |
|    2 | 2017-07-27 06:26:58 |                 |
|    3 | 2017-07-27 06:35:52 |          0.01   |
|    4 | 2017-07-27 06:36:56 |          0.05   |
|    5 | 2017-07-27 06:55:04 |          0.03   |

This query is what I've tried, but it doesn't work:

select A.cid, A.time, C.cpu 
  from A 
 INNER JOIN B on A.cid = B.CID2 
 where A.time ="2018-12-17 14:00:00";

I'm using mariadb.

Do you have any ideas?

I have also tried a query from @MikNiller, I don't want this, I want the same as below:

+------+---------------------+-----------------+
| CID2 | time                | cpu             |
+------+---------------------+-----------------+
|    1 | 2017-07-27 06:26:50 |       0.01      |
|    2 | 2017-07-27 06:26:58 |       0.05      |
|    3 | 2017-07-27 06:35:52 |       0.03      |
|    4 | 2017-07-27 06:36:56 |                 |
|    5 | 2017-07-27 06:55:04 |                 |

closed as unclear what you're asking by ypercubeᵀᴹ, hot2use, Paul White Feb 8 at 9:16

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Could you confirm which "result set" is the one you want, and which is the incorrect one? From the information provided it seems that you need to "left join" A and B (so that you get all the results from A) and output B.cpu in your results. The SELECT query in your example can't produce the first table (as it is using inner join which will only give the rows that match by CID). – seventyeightist Feb 5 at 19:31
  • 1
    Btw, the fact that you have both "ID" and "time" (for what appears to be the same values) in both tables, points to a possible issue in the structure of how you are storing this data... but that's outside the scope of your question and may or may not be something you have any control over! – seventyeightist Feb 5 at 19:34
  • You show two different result sets. Which of the two do you want? – ypercubeᵀᴹ Feb 7 at 22:20
0
 select A.cid, A.time, IF(A.cid = B.CID2,C.cpu,NULL) 
 from A 
 LEFT JOIN B on A.cid = B.CID2 
 where A.time ="2018-12-17 14:00:00";

Not the answer you're looking for? Browse other questions tagged or ask your own question.