MySQL Query Assistance

[stuart33]

Hi All,

Looking for some assistance with a SQL query.

I am trying to get my query to only show me results where there isn’t a status of 1 and 4 together. Hopefully I have explained below.

Users view the orders and apply for them. When they apply for the order, we ‘INSERT into tbl_applications’ with the status of 1, so I can see who applied for it by seeing the list of applications where Status = ‘1’ If a user doesn’t apply for it, we don’t see anything in the applications table.

When we select the user we are offering this to, we offer it to them and the status changes to 2. If they decline, the status is updated to 3. When the user accepts we change the status to 4.

At the moment, I can pull down the list of orders where there are no applications and I can pull down when there are applicants.

What I am struggling to do is, when the status of 4 is associated with the orderID, we don’t display this as available on the screen.

I am using the following query at the moment to pull through all the available orders.

SELECT * FROM tbl_orders
LEFT JOIN tbl_applications on tbl_applications.orderID = tbl_orders.orderID
WHERE (tbl_applications.Status IS NULL OR applications.Status = ‘1’ OR applications.Status = ‘2’ OR applications.Status = ‘3’)
group by tbl_orders.orderID

Schema:

tbl_users					 tbl_applications											 tbl_orders
+---------+----------+		+----------------+----------+---------+--------+ 			+----------+------------+
| userID  | userFN	 |		| applicationID  | orderID  | userID  | Status | 			|  orderID |   dateFor  |
+---------+----------+		+----------------+----------+---------+--------+			+----------+------------+
|       1 | Jane     |		|              1 |		  2	| 		2 |      1 |  			|        1 | 2019-06-01 |
|       2 | Lisa     |		|              2 |		  2	| 		1 |      2 |      		|        2 | 2019-07-01 |
|       3 | Scott    |		|              3 |		  3	| 		1 |      1 |     		|        3 | 2019-07-15 |
|       4 | Peter    |		|              4 |		  4	| 		1 |      1 |      		|        4 | 2019-07-25 |
|       5 | Julie    |		|              5 |		  6	| 		1 |      1 |       		|        5 | 2019-08-01 |     		
|       6 | Frank    |		|              6 |		  1	| 		1 |      1 |			|        6 | 2019-01-02 |
+---------+----------+      +----------------+----------+---------+--------+			+----------+------------+

[droopsnoot]

You might be better asking in the “databases” section as there is no PHP involved, I’m sure someone will move it for you.

[sibertius]

@stuart33

WHERE (tbl_applications.Status IS NULL OR applications.Status = ‘1’ OR applications.Status = ‘2’ OR applications.Status = ‘3’)

One way to shorten this is:

SELECT * FROM tbl_orders
LEFT JOIN tbl_applications on tbl_applications.orderID = tbl_orders.orderID
WHERE applications.Status IN (NULL,1,2,3)
AND users.userID = application.UserID

Assuming applications.Status is an integer column
Please do a http://www.sqlfiddle.com if this does not help.

[TechnoBear]

@droopsnoot

You might be better asking in the “databases” section

Moved.

[r937]

@sibertius

WHERE applications.Status IN (NULL,1,2,3)

that won’t work the way you think it will

yes, an IN list is the same as a series of equality tests all ORed together

but that means you get tbl_applications.Status = NULL and that won’t work

[r937]

@stuart33

I am using the following query at the moment to pull through all the available orders.

something’s wrong with what you posted, because group by shipments.shipmentID mentions a table (shipments) that is omitted from the FROM clause

[stuart33]

Apologies, the group by shipments.shipmentID should be tbl_orders.orderID

[r937]

why are you even grouping??

also, move your WHERE conditions to the ON clause of the join, that way you’ll avoid the ugly IS NULL test in the outer join

SELECT * 
  FROM tbl_orders
LEFT 
  JOIN tbl_applications 
    ON tbl_applications.orderID = tbl_orders.orderID
   AND tbl_applications.Status IN (1,2,3)

[stuart33]

Thanks!

I am trying to show only orders where the status doesn’t have a row of 1 and 4. It can still be shown if there were several status’ of 1, but once we get a row that contains the 4, we essentially hide it. Order 1 should now be hidden as we have a 4, whereas order 2 should still be shown.

[m_hutley]

GROUP BY orderID, WHERE MAX(Status) != 4?

[r937]

SELECT * 
  FROM tbl_orders
 WHERE NOT EXISTS
       ( SELECT orderID   
           FROM tbl_applications 
          WHERE orderID = tbl_orders.orderID
            AND Status IN (1,4)
         GROUP
             BY orderID
         HAVING COUNT(DISTINCT Status) = 2 )  

[m_hutley]

Also, I feel like this is a database architecture issue.

OrderID presumably links to an order table, which should be carrying its own Status…

[sibertius]

@r937

that won’t work the way you think it will

It is harder to grasp the problem when you not have the desired output and a full sqlfiddle to test your answer.

I guess you did read the question more carefully than I did.