Query mysql problem, what would be the best way to solve

Asked

Viewed 159 times

1

Hello, I am trying to make a query for an order system for a restaurant. My tables are arranged as follows:

Tabelas

I need to bring all tables (regardless of whether they have a request or not) if they have a status 0 order, these requests must come. If there is no request(s) with status 0 for that particular table, the request must come null.

For example:

Tables:

[1,1]
[2,1]
[3,1]
[4,1]

Orders:

[1,1,0]
[2,2,1]
[3,3,1]
[4,4,0]
[5,4,1]
[6,3,-1]
[7,2,0]

Expected result:

Table.id | Order.id 
1        |1
2        |null
3        |null
4        |4
2        |7
mysql sql query select
  • 1

    Search for Outer Join https://dev.mysql.com/doc/refman/5.7/en/outer-join-simplification.html

2 answers

3


You can use the comparator IF, see here.

SELECT tables.id as `TableID`, IF(orders.status = 0, orders.id, null) as `OrderID` FROM tables LEFT JOIN orders ON tables.id = orders.table_id

That way will return the id if the orders.status is precisely the 0 and will return null if the orders.status is different from 0.

You can also add this condition to LEFT JOIN, for example, recommend you read this for more information:

SELECT tables.id as `TableID`, pedido.id as `OrderID` FROM tables LEFT JOIN orders ON tables.id = orders.table_id AND orders.status = 0

That would join the tables when the tables.id is the same as tabled_id and also that the status for 0. Outside of this condition the tables would not be together, so would just return the data from tables while the remainder, corresponding to orders, would have the value of null.

  • Oh man, you did! Just one more thing... What if I wanted to bring all the data from the Orders table? Beyond the id. (With the same rule, if there is no request with status 0, the entire request should be null and void)

  • By default using LEFT JOIN he will return null where there is no data, so it could use ON tables.id = orders.table_id AND orders.status = 0, so that it is not equal to 0 would be null. Anyway you can use the IF(pedido.status = 0, pedido.UmColuna, null) as UmaColuna, this would return the value of UmaColuna or null.

  • Thank you very much man! It helped a lot!

0

Use: IFNULL

IFNULL(expr1, null)

In your case it would look like this:

SELECT tables.id as `TableID`, IFNULL(pedido.status, null) 
as `OrderID` 
FROM tables LEFT JOIN orders ON tables.id = orders.table_id

Note:The null substitution can be for any other value, not necessarily string.

Of documentation:

If Expr1 is not NULL, IFNULL() returns Expr1; otherwise returns expr2. IFNULL() returns a numerical value or string, depending on the context in which it is used.

Browser other questions tagged mysql sql query select

You are not signed in. Login or sign up in order to post.