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

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

CodePudding user response：

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 quieries:

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 quiery 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.

CodePudding user response：

You will need a group by on the A_ID so that it only returns when the total count of things with completed status matches the total count. So it appears this might be a manufacturing task and you may have 5 things going on. You only want to see the order when all 5 parts of the process are complete. Some tasks could be more or less.

select
      b.a_id,
      count(*) as NumberOfTasks
   from
      TableB b
   group by
      b.a_id
   HAVING
      count(*) = sum( case when b.status = 'COMPLETED' then 1 else 0 end )

So the counting is going to be per each a_id. The HAVING clause is applied at the end of the group aggregations, so the HAVING is saying... I want only those where the count of everything for the given A_ID = the sum of every sub-task that is marked as completed. You dont even need to join back to the root TableA to get the answer on this one. However, if you DID, you could add the join based on the A_ID.

CodePudding user response：

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

CodePudding user response：

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