How to query in mysql when involving more than one table?

Question

Here's my problem. I've two tables in the database (A and B). Table A consists of the primary ID (one) while table B consists of the status (Ongoing, Completed) of the ID in table A (status may be more than one). May I know how to get the data only when ALL the status of the ID(ORDER) is completed? I've included an image for further understanding.

Thank you for your time and help.

Table A
ID     NumberOfOrder
ABC1   2

Table B
ID     A_ID     Status
BEF1   ABC1     ONGOING
BEF2   ABC2     COMPLETED

Answer 1

Use JOIN statement.

For example:

SELECT A.ID, A.`NUMBER OF ORDER`, B.ID, B.STATUS 
FROM A 
INNER JOIN B ON A.ID = B.A_ID 
WHERE B.STATUS = 'COMPLETED';

Learn about inner and outer joins, they might be useful in different cases.

To output info only if EVERY status is COMPLETED, you might use nested queries:

SELECT A.ID, A.`NUMBER OF ORDER`, B.ID, B.STATUS 
FROM A 
INNER JOIN B ON A.ID = B.A_ID 
WHERE (SELECT COUNT(STATUS) FROM B WHERE B.STATUS <> 'COMPLETED') = 0;

In this example, first query to be executed will be:

SELECT COUNT(STATUS) 
FROM B 
WHERE B.STATUS <> 'COMPLETED'

It will return the amount of IDs with statuses NOT equal to COMPLETED.

After that, it will output every row from the joined table, where that amount equals to 0.

Since the amount is not depending on the row of the joined table, it will either output everything or nothing.

Answer 2

I think, you can try this query :

SELECT A.ID, A.'NUMBER OF ORDER', B.STATUS
FROM A
INNER JOIN B ON A.ID = B.A_ID
WHERE B.STATUS = 'COMPLETED'

more information about inner join, you can learn on W3S https://www.w3schools.com/sql/sql_join_inner.asp

Answer 3

Try this query:

SELECT A.ID, A.'NUMBER OF ORDER', B.STATUS
FROM B
LEFT JOIN A ON B.A_ID = A.ID 
WHERE B.STATUS = 'COMPLETED'