Help - MySQL Select Query

[SH1989]

Hi All,

Looking for a bit of assistance with a query. I have put the query below.

SELECT * FROM applications 
LEFT JOIN users on users.userID = applications.userID 
WHERE Order = '1' and (Status is null or Status != '2')

What happens is someone makes the application, and we insert into the applications table, with the status inserted to 1. When the application is offered the status is 2 and then the status is changed to 3 when it's accepted.

What I am trying to achieve is that when several applications will be made, and the status set to 1 (Several rows) and we will choose who to offer to and this row's status is updated to 2 and the rest are left as 1.

The problem I am having is that now we have multiple applications and when we offer it to a single person, we want to hide it from the list so we don't show it on screen, that's why I have checked to make sure the status is null (meaning no one applied) and then check to make sure the status is not 2 so it will show on screen, but can't seem to get it right.

Any advise would be great!

[Barand]

Can you provide the following

• Your table structures
• Some sample data which is representative of the problem
• The output you want from that sample data

[SH1989]

Thanks for getting back to me! I have attached the structure, with some sample data.

When the order is added to the table orders, we have an applications table (attached) When the application has been accepted, the status is updated to 4. By default the status is 1, meaning it's been applied for and 4 means it's been accepted.

What I am expecting is to see nothing presented to me on the dashboard as for each orderID there is a 4 which means that the order has been accepted and we don't need it on the dashboard anymore.

If all of the Status' were 1's I would still expect to see this on the dashboard, until we reach the 4.

 

Hopefully this makes a little sense!

[Barand]

If anything, it is now more confusing.

Your original post referred to "Applications" and "Users" tables. You also had ...

On 9/6/2019 at 9:56 AM, SH1989 said:

WHERE Order = '1'

... but this sample data has no "order" column.

This post refers to "Applications" and "Orders" tables

What does the entity relationship between these three tables look like? (Knowing what the data looks like and how it is related is a great to help to writing correct queries.)

And you still haven't told us what you want to see on the screen as a result of the query.

[Barand]

I am guessing here so correct me if I'm wrong, but I need to get you started.

If you had this

USER                         APPLICATION                                           ORDER
+---------+----------+       +----------------+----------+---------+--------+      +----------+------------+
| user_id | username |       | application_id | order_id | user_id | status |      | order_id | order_date |
+---------+----------+       +----------------+----------+---------+--------+      +----------+------------+
|       1 | Curly    |       |              1 |        1 |    NULL |   NULL |      |        1 | 2019-06-01 |
|       2 | Larry    |       |              2 |        2 |       1 |      1 |      |        2 | 2019-07-01 |
|       3 | Mo       |       |              3 |        2 |       2 |      1 |      |        3 | 2019-07-15 |
|       4 | Peter    |       |              4 |        2 |       3 |      1 |      |        4 | 2019-07-25 |
|       5 | Paul     |       |              5 |        3 |       4 |      2 |      |        5 | 2019-08-01 |
|       6 | Mary     |       |              6 |        3 |       5 |      1 |      |        6 | 2019-01-02 |
+---------+----------+       |              7 |        3 |       6 |      1 |      +----------+------------+
                             |              8 |        4 |       1 |      2 |      
                             |              9 |        5 |       2 |      2 |      
                             |             10 |        6 |       4 |      4 |
                             +----------------+----------+---------+--------+

then what output would you want to see?

[Barand]

On 9/6/2019 at 9:56 AM, SH1989 said:

and then the status is changed to 3 when it's accepted.

 

4 hours ago, SH1989 said:

When the application has been accepted, the status is updated to 4

Which is it?

Do know what's going on with your application?

[Barand]

Does this come close?

SELECT a.order_id
     , o.order_date
     , a.user_id
     , u.username
     , status
FROM application a
     INNER JOIN `order` o USING (order_id)
     LEFT JOIN user u USING (user_id)
     LEFT JOIN (
                SELECT order_id
                FROM application
                WHERE status IS NOT NULL AND status <> 1
               ) as stat USING (order_id)
WHERE stat.order_id IS NULL;

+----------+------------+---------+----------+--------+
| order_id | order_date | user_id | username | status |
+----------+------------+---------+----------+--------+
|        1 | 2019-06-01 |         |          |        |
|        2 | 2019-07-01 |       1 | Curly    |      1 |
|        2 | 2019-07-01 |       2 | Larry    |      1 |
|        2 | 2019-07-01 |       3 | Mo       |      1 |
+----------+------------+---------+----------+--------+

 

[SH1989]

Thanks for the replies and apologies for the confusion.

Quick overview: The order is added to the system. At this stage there is nothing in the applications table. Users can see the order and apply for it, which then inserts a 1 into the applications table (indicating they have applied), We can see the applicants for each order when we open it up.

There could be several users applying, so there will be several status' of 1. When we have chosen the successful applicant, we will update the status to 4.

So for arguments sake, we have 5 people apply, there will be 5 rows for that order within the applications table all with a status of 1. We will select the applicant and update the status to 4.

What I am trying to achieve as an output is that if there is a successful applicant for the order, we can hide it from the pending fulfilment list as we will have a status of 4 indicating that someone has the position.
 

USERS                         APPLICATION                                           ORDER
+---------+----------+       +----------------+----------+---------+--------+      +----------+------------+
| user_id | username |       | application_id | order_id | user_id | status |      | order_id | order_date |
+---------+----------+       +----------------+----------+---------+--------+      +----------+------------+
|       1 | Curly    |       |              1 |        1 |       1 |   	  1 |      |        1 | 2019-06-01 |
|       2 | Larry    |       |              2 |        1 |       2 |      4 |      |        2 | 2019-07-01 |
|       3 | Mo       |       |              3 |        1 |       3 |      1 |      |        3 | 2019-07-15 |
|       4 | Peter    |       |              4 |        1 |       4 |      1 |      |        4 | 2019-07-25 |
|       5 | Paul     |       |              5 |        1 |       5 |      1 |      |        5 | 2019-08-01 |
|       6 | Mary     |       |              6 |        2 |       1 |      1 |      |        6 | 2019-01-02 |
+---------+----------+       |              7 |        2 |       2 |      1 |      +----------+------------+
                             |              8 |        2 |       3 |      1 |      
                             |              9 |        2 |       4 |      1 |      
                             |             10 |        2 |       5 |      1 |
                             +----------------+----------+---------+--------+

Again, apologies for the confusion!

[Barand]

What about status=2 (offered). They seem to have disappeared from the scene.

Also NULL no longer seems to be an option and status 3 evaporated comlpletely a while ago.

(If this were a murder investigation you would be #1 suspect - every account you've given has been different, leaving me to try an hit a moving target.)

When you have finally decide what you want, adapt the query I gave you a couple of posts ago - it should be close.

Good luck.

Edited September 9, 2019 by Barand

[SH1989]

Thank for all your replies and help, Barand and apologies for the confusion.

What I am trying to achieve, is that I don't want to include in my results, any data where there is a Status of '1' AND '4' as this would mean that someone has applied (1) and someone has accepted(4), so it doesn't need to be displayed anymore.

Status 1 - Someone has applied for it.
Status 2 - when we have offered it.
Status 3 - if the user has declined it
Status 4 - Accepted by the user.

[Barand]

New "application" table trying to cover all the bases

+----------------+----------+---------+--------+
| application_id | order_id | user_id | status |
+----------------+----------+---------+--------+
|              1 |        1 |       1 |      1 |
+----------------+----------+---------+--------+
|              2 |        2 |       2 |      2 |
+----------------+----------+---------+--------+
|              3 |        3 |       3 |      3 |
+----------------+----------+---------+--------+
|              4 |        4 |       4 |      4 |    <--- ???
+----------------+----------+---------+--------+
|              5 |        5 |       5 |      1 |
|              6 |        5 |       6 |      2 |
+----------------+----------+---------+--------+
|              7 |        6 |       2 |      1 |
|              8 |        6 |       4 |      3 |
+----------------+----------+---------+--------+
|              9 |        7 |       1 |      1 |
|             10 |        7 |       6 |      4 |
+----------------+----------+---------+--------+
|             11 |        8 |       4 |      2 |
|             12 |        8 |       5 |      3 |
+----------------+----------+---------+--------+
|             13 |        9 |       2 |      3 |
|             14 |        9 |       3 |      4 |   <--- ???
+----------------+----------+---------+--------+

1 hour ago, SH1989 said:

I don't want to include in my results, any data where there is a Status of '1' AND '4'

Accordording to that rule, only order #7 should be excluded; orders #4 and #9 should not be excluded as they don't have "1" AND "4". Is that the case?

Can you let me know which of the above 9 orders you want to be displayed?