Select with Join alternative MYSQL

Asked

Viewed 34 times

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 from this table but this table can be related 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 listing I want to know the name of who made the inclusion of a record in the table, how would this query ?

mysql

  • The ideal would be to really remake from scratch with a JOIN really, but to take advantage of this one would be something like this: ... AND (log_tables_restaurants.id_admin = administrator.id OR logs_tables_restaurants.id_user = users.id) - What makes me strange is to have separate tables for admin and user, but then escapes the question.

  • How to get this part (Administrator.name OR users.first_name) using the logic you mentioned ?

1 answer

1


On the 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 on the part of WHERE, use parentheses and the OR to the alternative:

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

Browser other questions tagged mysql

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