Básico sobre inner join
Opa, e ai, blz?
A alguns dias estava conversando com um Brother que é programador, falando sobre códigos disse em certa hora que era só ele usar um Inner Join, que a query ficaria simples.
O cara vira e me diz “bicho, seu eu soubesse usar isso, sei que ficaria mais simples, mas, agora, vai ser tudo com and mesmo…”
Como achei tosco demais e estava com vontade de escrever pelo menos um básico sobre o assunto, enviei para ele o texto abaixo, exatamente como está…
———————————-8<———————————-
Segue uma amostra dos Inner join!!!! 🙂
Veja uma tabela que tenho de Planos, que contém um código e uma descrição:
mysql> select vc_plano, vc_descricao from table_planos where vc_plano=34; +----------+---------------+ | vc_plano | vc_descricao | +----------+---------------+ | 34 | Plano 0000034 | +----------+---------------+ 1 row in set (0.00 sec)
Agora, vou buscar na minha tabela de Clientes, um site com o plano 34:
mysql> select vc_domain, vc_plano from table_clientes where vc_domain like 'site.com.br'; +-------------+----------+ | vc_domain | vc_plano | +-------------+----------+ | site.com.br | 34 | +-------------+----------+ 1 row in set (0.00 sec)
Ok, agora, quero buscar nas 2 alguns dados, o site e a descrição do plano dele:
mysql> select a.vc_domain, b.vc_descricao from table_clientes a inner join table_planos b on (a.vc_plano = b.vc_plano) where a.vc_domain like 'site.com.br'; +-------------+---------------+ | vc_domain | vc_descricao | +-------------+---------------+ | site.com.br | Plano 0000034 | +-------------+---------------+ 1 row in set (0.00 sec)
Veja que retornei o site da tabela Clientes e a descrição da tabela Planos.
Esse seria o metodo mais simples de se escrever um inner join, escrevendo mesmo INNER JOIN….
Também é possível usando outra sintaxe, mas, essa é a que eu uso 🙂
Veja que comparo o mesmo campo (não precisariam ter o mesmo nome, mas ajuda) nas 2 tabelas.
A sintaxe é normalmente essa:
SELECT a.*, b.* FROM table a
INNER JOIN table b ON (a.campo = b.campo)
Veja a mesma busca, sem o where:
mysql> select a.vc_domain, b.vc_descricao from table_clientes a inner join table_planos b on (a.vc_plano = b.vc_plano) where a.vc_plano=34; +------------------------------+---------------+ | vc_domain | vc_descricao | +------------------------------+---------------+ | site.com.br | Plano 0000034 | | web-site2.com.br | Plano 0000034 | +------------------------------+---------------+ 2 rows in set (0.00 sec)
Legal, acho que agora, vc já sabe tudo de inner join 🙂
Vamos para mais um exemplo, agora, fazendo 2 inner joins.
Primeiro, vamos ver a tabelam STATUS:
mysql> select * from table_status; +-----------+-----------+ | dc_status | vc_status | +-----------+-----------+ | 1 | Ativo | | 2 | Suspenso | | 3 | Cancelado | | 4 | Novo | +-----------+-----------+ 4 rows in set (0.00 sec)
E, vamos revelar mais um campo da tabela clientes:
mysql> select vc_domain, vc_plano, dc_status from table_clientes where vc_domain like 'site.com.br'; +-------------+----------+-----------+ | vc_domain | vc_plano | dc_status | +-------------+----------+-----------+ | site.com.br | 34 | 1 | +-------------+----------+-----------+ 1 row in set (0.00 sec)
Agora, vamos trazer a descrição dos status a outra tabela:
mysql> select a.vc_domain, b.vc_descricao, c.vc_status from table_clientes a inner join table_planos b on (a.vc_plano = b.vc_plano) inner join table_status c on (a.dc_status = c.dc_status) where a.vc_plano=34; +------------------------------+---------------+-----------+ | vc_domain | vc_descricao | vc_status | +------------------------------+---------------+-----------+ | site.com.br | Plano 0000034 | Ativo | | web-site2.com.br | Plano 0000034 | Cancelado | +------------------------------+---------------+-----------+ 2 rows in set (0.00 sec)
Desse jeito, voce ganha muito nos sistemas, pois, não precisaria comparar strings,
você pode comparar os códigos e sempre que quiser mudar os valores de strings, altera
somente em uma tabela.
Ok, você já é um especialista.. Sabe tudo de inner join… mas…. seu DBA é um merda
e está cagando para a integridade do banco de dados..
Veja o que ele fez:
mysql> update table_clientes set dc_status=null where vc_domain='web-site2.com.br'; Query OK, 1 row affected, 1 warning (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 1
E veja o que ocorreu com a última query que você havia feito:
mysql> select a.vc_domain, b.vc_descricao, c.vc_status from table_clientes a inner join table_planos b on (a.vc_plano = b.vc_plano) inner join table_status c on (a.dc_status = c.dc_status) where a.vc_plano=34; +------------------------------+---------------+-----------+ | vc_domain | vc_descricao | vc_status | +------------------------------+---------------+-----------+ | site.com.br | Plano 0000034 | Ativo | +------------------------------+---------------+-----------+ 1 rows in set (0.00 sec)
CADE O web-site2.com.br !?!?!?!?!?!?!
Sei lá.. PERGUNTA PRO DBA!!! 🙂
Inner join, somente faz o que você manda.. igual um “and”.. ele sabe se é
para trazer um cara na comparação. Ou não.
Como agora o valor da coluna STATUS desse registro da tabela CLIENTE,
não é mais igual a nenhum valor da tabela STATUS, a comparação a = c não bate,
logo, ele não será retornado em suas buscas.
Mas, nada tema.. para esses casos existe uma solução.
Caso você queira trazer todos os valores, independente dos STATUS serem
iguais, você pode usar o irmão do inner join, o LEFT JOIN, veja:
mysql> select a.vc_domain, b.vc_descricao, c.vc_status from table_clientes a inner join table_planos b on (a.vc_plano = b.vc_plano) LEFT JOIN table_status c on (a.dc_status = c.dc_status) where a.vc_plano=34; +------------------------------+---------------+-----------+ | vc_domain | vc_descricao | vc_status | +------------------------------+---------------+-----------+ | site.com.br | Plano 0000034 | Ativo | | web-site2.com.br | Plano 0000034 | NULL | +------------------------------+---------------+-----------+ 2 rows in set (0.00 sec)
Ele segue as mesmas regras, a diferença é que ele retorna todos os resultados
que você pediu (where a.vc_plano=34), mesmo que a regra de comparação dele, não
exista.
Cara
Esse é um basição sobre isso, você sempre pode melhorar, procurar por exemplo
o right join… union e criar horrendas sub querys, o importante é funcionar! 🙂
Para finalizar, veja essa bizarrice:
$sqlteste2 = "select distinct a.products_id from products_to_categories a inner join products_description b on (a.products_id = b.products_id) inner join products_to_categories c on (a.categories_id=c.categories_id) where a.categories_id in (select categories_id from categories where parent_id in (select categories_id from categories_description where uf='$tbestados') or categories_id = (select categories_id from categories_description where uf='$tbestados')) and a.products_id in (select products_id from products_to_categories where categories_id='$categoria') order by b.products_name";
Isso somente significa… que as coisas podem ser pioradas ao extremo!!! 🙂
[]’s
Kerwin, olá!
Acredito que isso resolva seu problema:
select a.nomeusuario, b.nomegrupo as lider, c.nomegrupo as supervisor, d.nomegrupo as auxiliar
from usuarios a
left join grupos b on (a.id = b.idlider)
left join grupos c on (a.id = c.idsupervisor)
left join grupos d on (a.id = d.idauxiliar)
Usei essa base:
Tabela de usuarios:
Tabela de grupos:
E o resultado da query é:
[] s
Definitivamente sou devagar nesse negócio de relacionamento em banco de dados. Preciso de sua ajuda. Tenho duas tabelas bem simples: Usuários e Grupos.
Usuários contém (id, nomeusuario)
Grupos contém (id, idlider, idsupervisor, idauxiliar, nomegrupo)
Ou seja, cada grupo tem um supervisor, um líder e um auxiliar, e todos são usuários do sistema. Como fazer um select que me retorne os dados dos grupos, já com o nome dos respectivo líder, supervisor e auxiliar?
Valew Rodrigo!
Só preciso tomar vergonha na cara e atualizar/postar algo de vez em quando 🙂
Parabéns cara, material de excelente qualidade.
Simples e de fácil entendimento. Bem específico.
Muito obrigado!
Achei a explicação muito clara, mas sabe como e para quem nao sabe de programação continua sendo um caos isso.
Precisava de uma ajuda se possivel claro.
Estou criando um sistema de pedidos online para que os representantes possam fazer os pedidos de seus clientes diretamente no site e assim acelerar o processo.
O site e este gauersystem.cu.cc usuario:demo senha:demo todos podem entrar e ver como esta ficando e uma coisa simples mas to quebrando minha cabeça pois nunca estudei programação php ou mysql sempre utilizei joomla, wordpress ou outro cms.
meu problema e o seguinte como criar a parte dos pedidos, comissoes e tabelas(onde estarão os diferentes valores de cada produto variando a quantidade. Seria somente um site de pedidos onde aposa terminar o pedido ele enviaria por email tanto para o cliente quanto para o representante.
fico na duvida de como criar a variação de preços dependendo da quantidade de cada produto, a comissão e feita apartir da diferença dos valores cobrados ao da tabela de cada representante.
Fico no aguardo, qualquer duvida e so me mandar um email.
Perfeito, vou testar com UNION e ver como fica, vlw =].
Mas como eu precisava pegar somente a data mais recente, fiz um ORDER BY data1, data2 LIMIT 2.
Então comparei as 4 datas pelo php. Talvez com UNION, de alguns ms a menos.
Vlw Tiago, brigadU!
Olavo, boa noite.
Sim, esse comportamento é normal, ou seja, a ordenação ocorrerá na ordem mencionada, não será um comparativo entre os campos.
Isso porque, você estar ordenando campos totalmente distintos como data, nome, email, etc..
O MySQL (e outros bancos relacionais) irão fazer a ordenação na ordem que for passada.
Agora para resolver seu problema, acredito que você poderia usar uma tática mais simples, ao invés de ifs, você pode usar o UNION
da seguinte forma:
(SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY data1)
UNION
(SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY data2)
Ou ainda, para ficar mais fácil usar depois no programa:
(SELECT *, data1 as datanova FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY data1)
UNION
(SELECT *, data2 as datanova FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY data2)
Dessa forma você já tem um campo único ordenado, acredito eu..
[] s
Grande Tiago, sou eu denovo precisando de um help =]
Seguinte, preciso ordenar um select comparando as datas de 2 culunas, quero que ordene pela maior data, mas ta complicado. Tentei de varias maneiras e não acertei nenhuma.
Primeiro tentei assim:
SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY data1, data2
mas esta dando preferencia para data1, mesmo se data2 for maior.
então tentei assim:
SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY if(data1>data2 data1, data2)
e assim:
SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY if(data1>data2 data1 DESC)ELSE(data2 DESC)
E já sem opções, assim:
SELECT * FROM paginas WHERE site=’piadas’ AND status=1 ORDER BY if(data1>data2){data1 DESC}ELSE{data2 DESC}
Mas da erro de sintax. Não encontrei nada parecido. Não estou acertando a sintax ou não é a forma certa.
Obrigado pela atenção.
Vlw você, espero que tenha ido bem na prova!
[] s
Nuss cara você me salvou, segunda feira eu vou fazer uma prova de mysql sobre o inner join e tipo, tava super boiando até agora. Vlw
Aee, legal, bom que funcionou 🙂
Não tenho como testar o seu count.
Tente fazer a query isolada para chegar ao resultado desejado, depois fica fácil de colocar junto das outras.
[] s
Valeu tiago perfeito!!!
Só a conta dos sites que retorna varias linhas ai tive que tira.
então tentei faze assim:
“(SELECT COUNT(DISTINCT(site)) as site FROM paginas) as site”
mas retorna vazio.
Fico assim:
$query = “SELECT (SELECT COUNT(id) as cm_ttl FROM comentarios) as cm_ttl,
(SELECT SUM(cliques) as pc_ttl FROM paginas) as pc_ttl,
(SELECT COUNT(id) as pg_ttl FROM paginas) as pg_ttl,
(SELECT COUNT(id) as pd_ttl FROM produtos) as pd_ttl,
(SELECT COUNT(DISTINCT(site)) as site FROM paginas) as site”);
$X = mysql_query($query, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
extract($row_X);
return array($cm_ttl, $pc_ttl, $pg_ttl, $pd_ttl, $sites);
Olá Olavo!
Você pode fazer assim:
$query=”SELECT (select COUNT(id) as cm_ttl FROM comentarios) as cm_ttl,
(SELECT SUM(cliques) as pc_ttl FROM paginas) as pc_ttl,
(SELECT COUNT(id) as pg_ttl FROM paginas) as pg_ttl,
(SELECT COUNT(id) as pd_ttl FROM produtos) as pd_ttl,
(SELECT site FROM paginas GROUP BY site) as site”
Dai você busca exatamente como você precisa.
[]’s
Tiago, preciso de sua ajuda com o seguinte:
$query_X = sprintf(“SELECT COUNT(id) as total FROM comentarios”);
$X = mysql_query($query_X, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
echo $row_X[‘total’].’ comentários’;
$query_X = sprintf(“SELECT SUM(cliques) as total FROM paginas”);
$X = mysql_query($query_X, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
echo $row_X[‘total’].’ cliques’;
$query_X = sprintf(“SELECT COUNT(id) as total FROM paginas”);
$X = mysql_query($query_X, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
echo $row_X[‘total’].’ paginas’;
$query_X = sprintf(“SELECT COUNT(id) as total FROM produtos”);
$X = mysql_query($query_X, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
echo $row_X[‘total’].’ produtos’;
$query_X = sprintf(“SELECT * FROM paginas GROUP BY site”);
$X = mysql_query($query_X, $conexao) or die(mysql_error());
$row_X = mysql_fetch_assoc($X);
$total=mysql_num_rows($X);
echo $total.’ sites’;
são 5 consultas feitas em sequencia. Tem como fazer em uma só query?
tipo assim:
$query=”SELECT COUNT(id) as cm_ttl FROM comentarios,
SELECT SUM(cliques) as pc_ttl FROM paginas,
SELECT COUNT(id) as pg_ttl FROM paginas,
SELECT COUNT(id) as pd_ttl FROM produtos,
SELECT site FROM paginas GROUP BY site”
e eu imprimisse mais ou menos assim: $row[‘cm_ttl’].$row[‘pc_ttl’].$row[‘pg_ttl’].$row[‘pd_ttl’]
????
obg pela atenção.
Olá Mauro!
Seus testes estão usando sempre a mesma tabela e o mesmo campo: “tb_prato.prato_nome”
é sempre ele AS alguma coisa 🙂
Você teria como me passar uma pequena massa de dados para fazer uns testes?
[] s
oi
obrigado pelo post
estou com um problema e acredito que é capaz de resolver… estou farto de dar voltas mas não dá
as tabelas em questão são as seguintes
— —————————————————–
— Table `ptcbd_gr5`.`tb_prato`
— —————————————————–
CREATE TABLE IF NOT EXISTS `ptcbd_gr5`.`tb_prato` (
`idtb_prato` INT NOT NULL AUTO_INCREMENT ,
`prato_tipo` INT NOT NULL ,
`prato_nome` VARCHAR(40) NOT NULL ,
`prato_venergetico` INT NOT NULL ,
`prato_escola` INT NOT NULL ,
`prato_obs` VARCHAR(45) NULL ,
PRIMARY KEY (`idtb_prato`) ,
INDEX `prato_tipo` (`prato_tipo` ASC) ,
INDEX `prato_escola` (`prato_escola` ASC) ,
CONSTRAINT `prato_tipo`
FOREIGN KEY (`prato_tipo` )
REFERENCES `ptcbd_gr5`.`tb_tipoprato` (`idtb_tipoprato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `prato_escola`
FOREIGN KEY (`prato_escola` )
REFERENCES `ptcbd_gr5`.`tb_escolas` (`idescolas` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ”;
— —————————————————–
— Table `ptcbd_gr5`.`tb_itemprato`
— —————————————————–
CREATE TABLE IF NOT EXISTS `ptcbd_gr5`.`tb_itemprato` (
`idtb_itemprato` INT NOT NULL AUTO_INCREMENT ,
`prato_id_prato` INT NOT NULL ,
`prato_id_produto` INT NOT NULL ,
`prato_qtd` FLOAT NOT NULL ,
PRIMARY KEY (`idtb_itemprato`) ,
INDEX `prato_id_prato` (`prato_id_prato` ASC) ,
INDEX `prato_id_produto` (`prato_id_produto` ASC) ,
CONSTRAINT `prato_id_prato`
FOREIGN KEY (`prato_id_prato` )
REFERENCES `ptcbd_gr5`.`tb_prato` (`idtb_prato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `prato_id_produto`
FOREIGN KEY (`prato_id_produto` )
REFERENCES `ptcbd_gr5`.`tb_produto` (`idtb_produto` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ”;
— —————————————————–
— Table `ptcbd_gr5`.`tb_ementa`
— —————————————————–
CREATE TABLE IF NOT EXISTS `ptcbd_gr5`.`tb_ementa` (
`idtb_ementa` INT NOT NULL AUTO_INCREMENT ,
`ementa_data` DATE NOT NULL ,
`ementa_nome` VARCHAR(45) NOT NULL ,
`ementa_sopa` INT NULL ,
`ementa_prato` INT NULL ,
`ementa_prato1` INT NULL ,
`ementa_sobremesa` INT NULL ,
`ementa_escola` INT NOT NULL ,
PRIMARY KEY (`idtb_ementa`) ,
INDEX `ementa_sopa` (`ementa_sopa` ASC) ,
INDEX `ementa_prato1` (`ementa_prato1` ASC) ,
INDEX `sobremesa` (`ementa_sobremesa` ASC) ,
INDEX `ementa_escola` (`ementa_escola` ASC) ,
INDEX `ementa_prato` (`ementa_prato` ASC) ,
CONSTRAINT `ementa_sopa`
FOREIGN KEY (`ementa_sopa` )
REFERENCES `ptcbd_gr5`.`tb_prato` (`idtb_prato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `ementa_prato1`
FOREIGN KEY (`ementa_prato1` )
REFERENCES `ptcbd_gr5`.`tb_prato` (`idtb_prato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `sobremesa`
FOREIGN KEY (`ementa_sobremesa` )
REFERENCES `ptcbd_gr5`.`tb_prato` (`idtb_prato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `ementa_escola`
FOREIGN KEY (`ementa_escola` )
REFERENCES `ptcbd_gr5`.`tb_escolas` (`idescolas` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `ementa_prato`
FOREIGN KEY (`ementa_prato` )
REFERENCES `ptcbd_gr5`.`tb_prato` (`idtb_prato` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ”;
quando faço um select * tb_ementa
o resultado é mais ou menos o seguinte: data,nome,id,id,id,id
até aki mt bem
mas agora quero substituir os id pelo respectivo nome e fiz um inner join
SELECT escolas_nome AS ‘Escola’,ementa_nome AS ‘Nome’ ,tb_prato.prato_nome AS ‘Sopa’ , tb_prato.prato_nome AS ‘Prato’,tb_prato.prato_nome AS ‘Alternativa Prato’,tb_prato.prato_nome AS ‘Sobremesa’ FROM tb_ementa INNER JOIN (tb_prato,tb_escolas) ON ( ementa_sopa=idtb_prato and ementa_prato=idtb_prato and ementa_prato1=idtb_prato and ementa_sobremesa=idtb_prato);
acontece que n aparece nada …
se eu fizer
SELECT escolas_nome AS ‘Escola’,ementa_nome AS ‘Nome’ ,tb_prato.prato_nome AS ‘Sopa’ , tb_prato.prato_nome AS ‘Prato’,tb_prato.prato_nome AS ‘Alternativa Prato’,tb_prato.prato_nome AS ‘Sobremesa’ FROM tb_ementa INNER JOIN (tb_prato,tb_escolas) ON ( ementa_sopa=idtb_prato );
aparecem os pratos todos iguais …
como se o id fosse =
tem alguma solução para isto???
obg