Problem with query mysql, what would be the best way to solve

Question

Problem with query mysql, what would be the best way to solve

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

Hello, I'm trying to make an appointment for an ordering system for a restaurant. My tables are arranged as follows:

I need to bring all tables (regardless of whether or not they have a request) if they have a request with status 0, such requests should come. If there is no request (s) with status 0 for that particular table, the request should 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

asked by anonymous 25.01.2017 / 14:47

2 answers

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: Null substitution can be by any other value, not necessarily string.

From the documentation:

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

25.01.2017 / 15:14

Error with data display ArrayList When uploading, the filename turns the title

score 3 · Accepted Answer

You can use the IF comparer, see clicking 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

This will return id if orders.status is just 0 and will return null if orders.status is different from 0 .

You can also add this condition in LEFT JOIN , for example, I 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

This would join tables when tables.id is equal to tabled_id and also that status is 0 . Out of this condition the tables would not be joined , so it would only return the data of tables while the rest, corresponding to orders , would have the value of null .