MYSQL (People would like an example of how to list the services used for a client any x that has a dog with any name y)

Question

look I’m not getting to do needed an example. I just can’t do this consultation List the services used for a customer any x who has a dog with any name y.

create database pet;
use pet;

CREATE TABLE CLIENTE(
cod_cliente int primary key auto_increment,
nome varchar(100) not null,
telefone int,
cpf varchar(14) not null unique,
cel varchar(15) not null,
endereco varchar(100)not null
);

CREATE TABLE ANIMAL(
cod_animal int primary key auto_increment,
nome_animal varchar(100) not null,
sexo SET('M','F'),
idade int,
raca varchar (50)not null,
data_cadastro datetime not null 
);

CREATE TABLE FUNCIONARIO(
cod_funcionario int primary key auto_increment,
nome_funcionario varchar(100) not null,
cpf varchar(14) not null unique,
sexo SET('M','F'),
especialidade varchar(30) DEFAULT'tosador',
cel varchar(15) not null,
endereco varchar(100)not null
);

CREATE TABLE SERVICO_ANIMAL(
cod_cliente int,
cod_animal int,
cod_funcionario int,
FOREIGN KEY(cod_animal) REFERENCES ANIMAL(cod_animal),
FOREIGN KEY(cod_funcionario) REFERENCES FUNCIONARIO(cod_funcionario),
FOREIGN KEY(cod_cliente) REFERENCES CLIENTE(cod_cliente)
);

describe ;
INSERT INTO cliente(nome,telefone,cpf,cel,endereco) VALUES 
('roberto','32265849','78945622','999282127','rua andradas'),
('reginaldo','32228949','78989437','999282237','rua esmeralda'),
('ana paula','32547165','78254883','999456821','rua juca'),
('dagoberto','32123654','78854713','999126845','rua tereza'),
('roberto','22132547','78456927','99987594600','rua santana');

INSERT INTO animal(nome_animal,sexo,idade,raca,data_cadastro) VALUES 
('toby','F','5','poodle','2017-06-21'),
('neco','M','2','chow chow','2017-07-27'),
('nick','M','1','pastor','2017-10-27'),
('luqui','M','7','salsicha','2017-04-26'),
('chica','F','15','pinther','2017-10-17');

INSERT INTO funcionario(nome_funcionario,cpf,sexo,especialidade,cel,endereco) VALUES 
('roberta','0075555849','F','tosador','(52)355555569','rua dos andres'),
('roberto','0032265849','M','tosador','(51)548987469','rua gonalves'),
('joao','0032846569','M','tosador','(51)355956237','rua terneira'),
('reginaldo','0785465849','M','tosador','(51)355978946','rua assis'),
('reginaldinha','0756789789','M','tosador','(51)355546786','rua nogueira');

Answer 1

There goes the fingertip!

I advise changing the structure of your table to improve performance and use this querie:

SELECT * FROM CLIENTES C INNER JOIN ANIMAL A ON C.ID_CLIENTE = A.ID_CLIENTE 
INNER JOIN SERVICO_ANIMAL SA ON C.COD_CLIENTE = SA.COD_CLIENTE WHERE 
C.NOME = 'X' AND A.NOME_ANIMAL = 'Y';

This querie below solves what you want in the structure of tables that you have shown up there however I advise against this structure because it should relate the animal table to the client (since the animal does not exist without a client. Correct?) with foreign key, if in the future you need to develop other modules avoid another Inner Join in the queries which gives great difference in performance.

That darling should solve your problem

SELECT * FROM SERVICO_ANIMAL SA INNER JOIN CLIENTES C ON SA.COD_CLIENTE = 
C.COD_CLIENTE AND C.NOME= 'X' INNER JOIN ANIMAL A ON 
SA.COD_CLIENTE = A.ID_ANIMAL AND A.NOME_ANIMAL = 'Y'  

I hope this is useful! Hugs...