I try to get this query different

Question

I try to get this query different

Navigation

#1 by (2 votes)

2

I need to draw from these three tables:

user:

user_type

restaurant:

all those restaurants that do not belong or do not have a type_user 1 (owner), that is to say in this case I would need to take out restaurant winery and restaaa that they have no owner user.

What I have tried is this but nothing ....

select r.* 
  from restaurante r
 inner join usuario u 
    on u.id_restaurante = r.id 
  left join tipo_usuario t
    on t.id = u.id_tipo_usuario
 where u.id_tipo_usuario = 1 
   and u.id_restaurante is null

mysql sql

asked by T P 21.05.2017 в 18:26

source

1 answer

Error using SUM in MySQL and displaying by groups Capture value of a TextBox and check that it is equal to another value in C #

score 2 · Answer 1

You only need to scroll through your restaurant table and with the sub select exclude users that meet the condition you need to remove.

select * from restaurante 
   where id not in ( select id_restaurante from usuario 
                      where id_tipo_usuario = 1 or id_restaurante is null );

Previously my answer was this, but I did not like it at all:

select r.* from restaurante r inner join usuario u 
         on u.id_restaurante = r.id 
                              left join tipo_usuario t 
         on t.id = u.id_tipo_usuario 
       where u.id_restaurante not in ( select id_restaurante from usuario 
                                           where id_tipo_usuario = 1 
                                             or  id_restaurante is null);