SELECT with Join Join MYSQL

Question

SELECT with Join Join MYSQL

Navigation

#1 by (1 votes)

0

Table - log_tables_restaurants

id    |  id_restaurant  | id_admin   |  id_user  | action 
 1    | 10              |   2        |  NULL     | adicionou 2 mesas
 2    | 11              |   3        |  NULL     | removeu 2 mesas
 3    | 20              |   NULL     |  30       | adicionou 10 mesas

I have to make a select that lists the data of this table, but this table can relate either to the administrator table or to the users table to know who made the record.

SELECT log_tables_restaurants.id
      ,log_tables_restaurants.id_restaurant
      ,**administrator.name OU users.first_name**
      ,log_tables_restaurants.day_week
      ,log_tables_restaurants.action
      ,log_tables_restaurants.created_at 
  FROM log_tables_restaurants, restaurants, administrator 
 WHERE ((id_region = '1')) 
   AND log_tables_restaurants.id_restaurant = restaurants.id 
   AND log_tables_restaurants.id_admin = administrator.id **OU logs_tables_restaurants.id_user = users.id**

My question is where I marked with bold, since in the list I want to know the name of who made the inclusion of a record in the table, what would that query look like?

mysql

asked by anonymous 20.09.2018 / 13:47

1 answer

Function authorizing or not submitting form View most current record sql server 2008

score 1 · Accepted Answer

In part of SELECT , to get the name:

COALESCE(administrator.name, users.first_name) AS nome

Or, you can use an IF:

IF(log_tables_restaurants.id_admin IS NULL, users.first_name, administrator.name) AS nome

And in the WHERE part, parentheses and OR are used for the alternative:

... AND (
       log_tables_restaurants.id_admin = administrator.id
       OR
       logs_tables_restaurants.id_user = users.id
    )