COUNT e SUM com 3 tabelas

Eu estou fazendo um sistema de gerenciamento de TI. Tenho 3 tabelas: computers, offices, office_keys

inserir a descrição da imagem aqui

Exemplo:

offices ______________________________________ id      | 1 version | Home & Business 2016   office_keys _______________________________________ id        | 1 office_id | 1 key       | AAAA-BBBB-CCCC-DDDD-EEEE   computers _______________________________________ id            | 1 name          | DESKTOP office_key_id | 1 

O campo quantity da tabela office_keys representa o número de vezes que aquela chave pode ser usada em computadores (office standard).

Eu quero fazer uma query que traga todas as versões do office, a soma da quantidade das keys (quantity) e soma de quantas chaves estão sendo usada (somente a soma).

Por exemplo:

Se eu tiver 4 chaves de office 2016 e 2 computadores utilizando 2 dessas chaves, ou se eu tiver 3 chaves de office 2013 e 3 computadores utilizando essas 3 chaves, quero receber:

Office   |    Quantidade   |   Em uso 2016     |    4            |   2 2013     |    3            |   3 

Tentei fazer isso:

SELECT DISTINCT offices.id, offices.version, SUM(office_keys.quantity) AS quantity, COUNT(computers.id) AS in_use FROM offices LEFT JOIN office_keys ON office_keys.office_id = offices.id LEFT JOIN computers ON computers.office_key_id = office_keys.id GROUP BY offices.id 

Mas a somas não estão certas por causa das linhas duplicadas.

Como posso resolver isso? Obrigado!

Comparação entre duas tabelas SQL

Boa tarde.

Como faço para fazer a comparação entre duas tabelas SQL? Sendo que:

  • Existem duas tabelas de medicamentos, uma contendo o nome do medicamento em inglês e outra em português.
  • As características (princípios ativos) dos medicamentos estarão na mesma língua.
  • Preciso ler o nome do medicamento em português, e buscar medicamentos com os mesmos princípios ativos na outra tabela e retornar o nome do medicamento da outra tabela, ou seja, em inglês.

Por exemplo:

Tabela em Português

Nome Remédio | Princípio Ativo

Buscopan – 10 mg de butilbrometo, 2 mg de ácido acético

Tabela em Inglês

Nome Remédio | Princípio Ativo

Hyoscine butylbromide – 10 mg de butilbrometo, 2 mg de ácido acético

Entrada: Buscopan Saída: Hyoscine butylbromide

Estou fazendo em JS.

Obrigado se puderem ajudar.

Como configurar os relacionamentos entre tabelas (e fazer query com withRelated) no ORM Bookshelf.js?

Seguindo a documentação do Bookshelf.js (https://bookshelfjs.org), eu criei a minha conexão com a base de dados Mysql normalmente:

const knex = require("knex")({   client: "mysql",   connection: {     host: "localhost",     user: "root",     password: "keydb",     database: "desenrola_app",     charset: "utf8"   } });  const bookshelf = require("bookshelf")(knex);  module.exports = bookshelf;

Logo em seguida, eu defini duas instâncias de tabelas que estão criadas (e relacionadas por FK, onde customer_person_id da tabela customer faz referência a id da tabela person.

Segue os models:

const db = require("./db"); const Customer = require("./customer");  const Person = db.Model.extend({   tableName: "person",   customer() {     return this.hasOne(Customer, "customer_person_id", "id");   } });  module.exports = Person;

e

const db = require("./db"); const Person = require("./person");  const Customer = db.Model.extend({   tableName: "customer",   person() {     return this.belongsTo(Person, "customer_person_id", "id");   } });  module.exports = Customer;

No controller, onde eu exporto a função findOne para as minhas rotas (esta parte está OK, cai no CATCH da função ou simplesmente retorna JSON vazio no Postman), executo a seguinte query:

const Customer = require("../models/customer");  exports.findOne = async (req, res, next) => {   const id = req.body.id;    await new Customer({ customer_person_id: id })     .fetch({ withRelated: ["person"] })     .then(function(client) {       res.send(client.related("person").toJson());     })     .catch(err => res.json(err)); };

Eu gostaria de saber se estou fazendo o relacionamento one-to-one de forma equivocada, ou estou pecando em alguma regra. Bem, não sei mesmo o motivo de esta query não me retornar algo, uma vez que em meu banco de dados tenho na tabela customer a FK preenchida com o mesmo valor de ID na tabela person.

Permissão de DROP de tabelas em bancos SQL Server

Ao executar o comando (T-SQL) de DROP abaixo no SQL server é exibido a seguinte mensagem:

Cannot drop the table ‘[dbo].[TABELA]’, because it does not exist or you do not have permission.

Comando

EXEC sp_MSForEachTable 'DROP TABLE [?]' 

Entretanto ao executar outro comando T-SQL ou um DROP Table básico a mensagem de erro acima não é exibida, sendo o comando executado corretamente.

DROP TABLE ArquivosFeiras EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all' 

Ambos os comandos acima funcionam, qual o motivo do EXEC sp_MSForEachTable 'DROP TABLE [?]' necessitar de uma permissão? e como dar esta permissão?

Inserção de dados em duas tabelas em um único formulário

Olá, bom dia! Estou estudando WebForm na faculdade, e estou com uma dúvida para inserir um valor de um única pagina de formulário para duas Tabelas. No caso para exemplificar, tenho duas tabelas “Pessoa” e “Cliente”. A tabela pes_pessoa tem os campos (pes_id, pes_nome) e cli_cliente (cli_id, pes_id, pes_observação). Porém não sei como inserir estes dados, pois necessito pela lógica inserir a pessoa para ter o id (por ser uma FK) para conseguir inserir um cliente. Seria dois INSERTS simultâneo na classe da Persistência? Eu sei, pela pesquisa de internet utilizar o ExecuteScalar() para pegar o último registro inserido da pessoa, mas não sei como cadastrar o cliente com estes dados. Meu código está assim na classe PessoaDB p/ inserir.

public static int Inserir(Pessoa pes) {     int retorno = 0;     try     {         IDbConnection objConexao;         IDbCommand objComando;         objConexao = Mapped.Connection();         string sql = "INSERT INTO pes_pessoa(pes_nome) VALUES (?pes_nome) ; SELECT LAST_INSERT_ID(); ";         objComando = Mapped.Command(sql, objConexao);         objComando.Parameters.Add(Mapped.Parameter("?pes_id", pes.Id));         objComando.Parameters.Add(Mapped.Parameter("?pes_nome", pes.Nome));         retorno = Convert.ToInt32(objComando.ExecuteScalar());          objConexao.Close();         objComando.Dispose();         objConexao.Dispose();     }     catch (Exception e)     {         retorno = -2;     }     return retorno; } 

Classe ClienteDB

public static int Inserir(Cliente cli) {     int retorno = 0;     try     {         IDbConnection objConexao;         IDbCommand objComando;         objConexao = Mapped.Connection();         string sql = "INSERT INTO cli_cliente( cli_observacao, pes_id) VALUES ( ?cli_observacao, ?pes_id) ; SELECT LAST_INSERT_ID(); ";         objComando = Mapped.Command(sql, objConexao);         objComando.Parameters.Add(Mapped.Parameter("?cli_id", cli.Id));         objComando.Parameters.Add(Mapped.Parameter("?cli_observacao", cli.Observacao));         objComando.Parameters.Add(Mapped.Parameter("?pes_id", cli.Pessoa));         retorno = Convert.ToInt32(objComando.ExecuteScalar());         objConexao.Close();         objComando.Dispose();         objConexao.Dispose();     }     catch (Exception e)     {         retorno = -2;     }     return retorno; } 

Update com duas tabelas ao mesmo tempo

Estou fazendo um update para janela de edição e não está dando erro, mas está funcionando somente a parte de endereço, a parte das informações do apicultor não está atualizando

<?php  session_start(); require_once "conexao.php";   $  nome_apicultor = $  _POST['nome_apicultor']; $  sobrenome_apicultor = $  _POST['sobrenome_apicultor']; $  email_apicultor = $  _POST['email_apicultor']; $  cpf_apicultor = $  _POST['cpf_apicultor']; $  rua_apicultor = $  _POST['rua_apicultor']; $  numero_apicultor = $  _POST['numero_apicultor']; $  bairro_apicultor = $  _POST['bairro_apicultor']; $  cidade_apicultor = $  _POST['cidade_apicultor']; $  complemento_apicultor = $  _POST['complemento_apicultor']; $  telefone_apicultor = $  _POST['telefone_apicultor']; $  estado_apicultor = $  _POST['estado_apicultor']; $  codEndereco = $  _POST['codEndereco']; $  codApicultor = $  _SESSION["token"];    try { 	$  prepared = $  conexao_pdo->prepare("UPDATE `endereco` SET `endereco` = :rua_apicultor, `bairro` = :bairro_apicultor, `numero` = :numero_apicultor, `complemento` = :complemento_apicultor, `cidade` = :cidade_apicultor, `estado` = :estado_apicultor  where `cod` = :codEndereco;"); 	$  prepared->bindParam(":rua_apicultor", $  rua_apicultor); 	$  prepared->bindParam(":bairro_apicultor", $  bairro_apicultor); 	$  prepared->bindParam(":numero_apicultor", $  numero_apicultor); 	$  prepared->bindParam(":complemento_apicultor", $  complemento_apicultor); 	$  prepared->bindParam(":cidade_apicultor", $  cidade_apicultor); 	$  prepared->bindParam(":estado_apicultor", $  estado_apicultor); 	$  prepared->bindParam(":codEndereco", $  codEndereco); 	$  prepared->execute(); 	$  id_end = $  conexao_pdo->lastInsertId();  	if($  prepared->rowCount() > 0) { 		$  prepared = $  conexao_pdo->prepare("UPDATE `apicultor` SET `nome` = :nome_apicultor, `sobrenome` = :sobrenome_apicultor, `email` = :email_apicultor, `cpf` = :cpf_apicultor, `telefone` = :telefone_apicultor, `endereco_cod` = :endereco_cod  where `cod` = :codApicultor;"); 		$  prepared->bindParam(":nome_apicultor", $  nome_apicultor); 		$  prepared->bindParam(":sobrenome_apicultor", $  sobrenome_apicultor); 		$  prepared->bindParam(":email_apicultor", $  email_apicultor); 		$  prepared->bindParam(":cpf_apicultor", $  cpf_apicultor); 		$  prepared->bindParam(":telefone_apicultor", $  telefone_apicultor); 		$  prepared->bindParam(":codApicultor", $  codApicultor); 		$  prepared->bindParam(":endereco_cod", $  id_end); 		$  prepared->execute(); 		 		  		if($  prepared->rowCount() > 0) { 			header("Location: ../Perfil.php"); 		} 		else { 			header("Location: ../Perfil.php"); 			 		} 	} 	else { 		header("Location: ../Perfil.php"); 	} 	 } catch(PDOException $  e) { 	print $  e->getMessages(); 	die(); }  ?>

Porque é que o EF Core está a acttualizar dados de outras tabelas?

Porque é que o EF Core está a acttualizar dados de outras tabelas?

Estou a actualizar uma tabela com o EF mas no entanto eu reparo que o EF está a actualizar essa tabela e ainda outras tabelas.

 public async Task<IActionResult> Edit(int id, [Bind("Cor,Porte,Raca,Vacinado,Sexo,TipoAnimal,TipoAnuncio, Id,Descricao,Aprovado,DataPublicacao,Telefone,Email,Idade, Pais, Localidade")] Anuncio anuncio, IEnumerable<IFormFile> Imagens)  {             if (id != anuncio.Id)             {                 return NotFound();             }             var anuncioActualizado = await _context.Anuncio.Include(x => x.Cor)                .Include(x => x.Imagens)                .Include(x => x.Localidade)                .Include(x => x.Pais)                .Include(x => x.Porte)                .Include(x => x.Raca)                .Include(x => x.Sexo)                .Include(x => x.TipoAnimal)                .Include(x => x.TipoAnuncio)                .Include(x => x.User)                .Include(x => x.Vacinado)                .Include(x => x.Cor).Where(x => x.Id == id).FirstAsync();              var userid = User.FindFirst(ClaimTypes.NameIdentifier).Value;             anuncioActualizado.Cor.Nome = _context.Cor.Where(x => x.Id == anuncio.Cor.Id).FirstOrDefault().Nome;             anuncioActualizado.Porte.Nome = _context.Porte.Where(x => x.Id == anuncio.Porte.Id).FirstOrDefault().Nome;             anuncioActualizado.Sexo.Nome = _context.Sexo.Where(x => x.Id == anuncio.Sexo.Id).FirstOrDefault().Nome;             anuncioActualizado.TipoAnimal.Nome = _context.TipoAnimal.Where(x => x.Id == anuncio.TipoAnimal.Id).FirstOrDefault().Nome;             anuncioActualizado.TipoAnuncio.Nome = _context.TipoAnuncio.Where(x => x.Id == anuncio.TipoAnuncio.Id).FirstOrDefault().Nome;             anuncioActualizado.Vacinado.Nome = _context.Vacinado.Where(x => x.Id == anuncio.Vacinado.Id).FirstOrDefault().Nome;             anuncioActualizado.Raca.Nome = _context.Raca.Where(x => x.Id == anuncio.Raca.Id).FirstOrDefault().Nome;             anuncioActualizado.User = _context.Users.Where(x => x.Id == _userId).First();             anuncioActualizado.Pais.Nome = _context.Pais.Where(x => x.Id == anuncio.Pais.Id).FirstOrDefault().Nome;             anuncioActualizado.Localidade.Nome = _context.Localidade.Where(x => x.Id == anuncio.Localidade.Id).FirstOrDefault().Nome;             anuncioActualizado.DataPublicacao = DateTime.Now;             anuncioActualizado.Descricao = anuncio.Descricao;             anuncioActualizado.Email = anuncio.Email;             anuncioActualizado.Idade = anuncio.Idade;               if (Imagens != null)             await ActualizarImagemAsync(anuncioActualizado, Imagens);             try             {                 _context.Anuncio.Update(anuncioActualizado);                 await _context.SaveChangesAsync();             }             catch (DbUpdateConcurrencyException)             {                 if (!AnuncioExists(anuncio.Id))                 {                     return NotFound();                 }                 else                 {                     throw;                 }             }             return RedirectToAction("Anuncio", "Anuncios", new { id = anuncio.Id });          } 

Então eu apenas quero actualizar a entidade Anuncio No entanto esta está a actualizar as outras entidades como “Cor”, “Porte” etc

 public class Anuncio: Animal     {         public int Id { get; set; }         [Required]         public string Descricao { get; set; }         [Required]         public TipoAnuncio TipoAnuncio { get; set; }         [Required]         public bool Aprovado { get; set; }         [Required]         public DateTime DataPublicacao { get; set; }         [Required]         public virtual ICollection<Imagem> Imagens { get; set; }         [Required]         public int Telefone { get; set; }         [EmailAddress]         public string Email { get; set; }         [Required]         public Pais Pais { get; set; }          [Required]         public Localidade Localidade { get; set; }          public ApplicationUser User { get; set; }      } 

As outras entidades estão todas configuradas assim

public class Pais     {         public int Id { get; set; }         [Required]         public string Nome { get; set; }      } 

Como fazer o relacionamento de tabelas entre Aluno Livro para Aluno?

Irei começar a fazer um trabalho que tenho que criar um aplicativo de doações, trocas e empréstimos de livros. Não tenho muito conhecimento com banco de dados e esse app deve ser o mais básico possível. Pela minha analise, esse relacionamento seria 1-N, já que um aluno pode (doar, trocar ou emprestar) 0 ou várias livros, mas um livro pode ser (doado, trocado ou emprestado) por 0 ou no máximo 1 aluno. Além disso, a situação do livro é multi-valorada, ou seja, 3 possibilidades, já que as ações tomadas, doar, trocar e emprestar são diferentes. Existe a possibilidade de eu fazer sem ser multi-valorado e tratar cada caso?

SELECT em múltiplas tabelas para diferentes campos com dados em comum no MySQL

O banco de dados tem 9 tabelas e cada tabela tem os nomes dos campos diferentes das outras tabelas, o que tem em comum é que na maioria existem campos para um token, titulo e descrição, o SELECT fiz para buscar registros que coincidem com a entrada do usuário, que pode ser um número de token de valores inteiros ou strings.

o SELECT é executado como o abaixo e não resulta em nada, e nem erros apresentam, se não dá erro, e não lista registros e os registros existem, nesse caso, somente um token deve existir em qualquer tabela, ou a string que pode haver em mais de uma tabela, mas existe.

Em todas as tabelas o tipo do campo é o mesmo para token, para a descrição também é o mesmo e algumas não possuem o campo título. Para os campos com token fiz a instrução para campo = ‘VALOR’ e para os outros campos como título e descrição, fiz campo LIKE ‘VALOR’, pois nos dois últimos podem existir mais de um registro.

Não usei ‘%VALOR%’ em título e descrição pois mesmo que haja mais de um registro, devem ser iguais à entrada do usuário, independente da posição das strings dentro dos campos.

SELECT      an.`anuncio_id`,     ar.`ar_id`,     ca.`ca_id`,     im.`im_id`,     mo.`mo_id`,     na.`na_id`,     te.`te_id`,     tr.`tr_id`,     ve.`ve_id` FROM      `anuncio` AS an,     `tb_ar` AS ar,     `tb_ca` AS ca,     `tb_im` AS im,     `tb_mo` AS mo,     `tb_na` AS na,     `tb_te` AS te,     `tb_tr` AS tr,     `tb_ve` AS ve WHERE      an.`anuncio_titulo`  LIKE      '280089540172' OR      an.`anuncio_descricao`  LIKE      '280089540172' OR      an.`anuncio_token`  =      '280089540172' OR      ar.`ar_token`  LIKE      '280089540172' OR      ar.`ar_descricao`  LIKE      '280089540172'  OR      ca.`ca_token`  =      '280089540172' OR      ca.`ca_descricao`  LIKE      '280089540172' OR      im.`im_token`  =      '280089540172' OR      im.`im_titulo`  LIKE      '280089540172' OR      im.`im_descricao`  LIKE      '280089540172' OR      mo.`mo_token`  LIKE      '280089540172' OR      mo.`mo_descricao`  LIKE      '280089540172' OR      na.`na_token`  =      '280089540172' OR      na.`na_titulo`  LIKE      '280089540172' OR      na.`na_descricao`  LIKE      '280089540172' OR      te.`te_token`  =      '280089540172' OR      te.`te_titulo`  LIKE      '280089540172' OR      te.`te_descricao`  LIKE      '280089540172' OR      tr.`tr_token`  =      '280089540172' OR      tr.`tr_titulo`  LIKE      '280089540172' OR      tr.`tr_descricao`  LIKE      '280089540172' OR      ve.`ve_token`  =      '280089540172' OR      ve.`ve_descricao`  LIKE      '280089540172'  LIMIT      100 ; 

Tentei adaptar a query para uma ajuda que tive um tempo atrás fazendo uso de UNION, mas quando retornava mais de um resultado aí dava problema pois não vinham todos os registros, somente para uma condição.