Jump to content

Help - MySQL Select Query


SH1989

Recommended Posts

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!

Link to comment
Share on other sites

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!

Img.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

If you had this

image.png.4ce97b03a540db4d53a7d70cc712ba60.png


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?

Link to comment
Share on other sites

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 |
+----------+------------+---------+----------+--------+

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.