Select em duas tabelas

[Cristian_ 0]

Boa tarde,

 

Pessoal, estou com um problema aqui.

Tenho duas tabelas, uma de visitantes e uma de controle de visitas:

 

VISITANTES

codVisitante

nomeVisitante

rgVisitante

 

CONTROLE

codVisita

nomeVisitante

rgVisitante

flag

 

Na página tenho um campo que faz a busca na tabela VISITANTES com base no nome ou rg digitado:

 

SELECT * FROM visitantes WHERE nomeVisitante LIKE %nome% OR rgVisitante LIKE %rg%

 

Queria adicionar o campo flag da tabela CONTROLE neste select de forma a checar se o visitante já entrou ou não. Imagino que seria isso:

 

SELECT * FROM visitantes INNER JOIN controle WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Mas não dá certo...

 

A idéia é trazer apenas os nomes dos visitantes que não entraram ainda (flag = 0).

 

Alguém pode me dar uma ajuda?

[spiderpoison 0]

VISITANTES

codVisitante

nomeVisitante

rgVisitante

 

CONTROLE

codVisita

nomeVisitante

rgVisitante

flag

 

SELECT * FROM visitantes INNER JOIN controle ON controle.codVisita = visitante.codVisitante WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

[Victor Cometti 8]

sua modelagem não está boa, você está duplicando a mesma informação em 2 tabelas (nomeVisitante e rgVisitante)

 

poderia simplesmente usar o id do visitante na tabela Controle

 

VISITANTES

codVisitante

nomeVisitante

rgVisitante

 

CONTROLE

codVisita

codVisitante (chave extrangeira)

flag

 

e na consulta

SELECT `v`.*, if (`c`.`flag` IS NULL, 0, 1) as flag
FROM `VISITANTES` v LEFT JOIN `CONTROLE` c ON `v`.`codVisitante` = `c`.`codVisitante` where .....
GROUP BY `v`.`codVisitante`

[Cristian_ 0]

Olá Spider,

 

A tabela CONTROLE é para controlar as visitas e por esse motivo a coluna 'codVisita' é a chave primária e não tem ligação com a 'codVisitante' da tabela VISITANTES

 

Tentei usar seu código assim:

 

SELECT * FROM visitantes INNER JOIN controle ON controle.nomeVisitante = visitantes.nomeVisitante WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Mas retorna um erro:

 

MySQL Error#: 1052

Column 'nomeVisitante' in order clause is ambiguous

 

Olá Victor,

 

O fato de duplicar a coluna rgVisitante foi o copy/past que dei.

 

Entendo que deveria usar o codVisitante como chave estrangeira porém no sistema há a necessidade de deixar em aberto a possibilidade de excluir visitantes pelo administrador, ou seja, se referenciar os dados do visitante apenas pelo codigo dele e um dia o mesmo for apagado eu perco o registro de quem esteve no local em determinada data pois minha consulta retornaria apenas o codigo do mesmo gravado na tabela controle.

 

Por isso estou considerando o nomeVisitante como ligação das tabelas...

 

Tentei usar assim:

 

SELECT `v`.*, if (`c`.`flag` IS_NULL, 0, 1) as flag

FROM `VISITANTES` v LEFT JOIN `CONTROLE` c ON `v`.`nomeVisitante` = `c`.`nomeVisitante` WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Mas recebi um erro:

 

[Victor Cometti 8]

o erro foi falha minha. usa IS NULL e não IS_NULL

 

agora em relação a apagar o registro. para seu banco não ficar inconsistente use contraints(restrições) para apagar registros não permitindo que seja feito caso haja relação com o mesmo em outra tabela. ou apague tudo relacionado.

[wesleybob4 0]

Seu código :

SELECT * FROM visitantes INNER JOIN controle WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

 

 

 

 

Meu Código:

 

SELECT visitantes.codVisitante, visitantes.nomeVisitante, visitantes.rgVisitante, controle.codVisita, controle.codVisitante, controle.flag

FROM visitantes

INNER JOIN controle

on controle.nomeVisitante = visitantes.nomeVisitante

WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Me fale os campos que quer amostrar e observação:

 

Boas práticas de Programação

Faça sua tabela mais prática e fácil de entender: Assim

 

VISITANTES

vis_cod

vis_nome

vis_rg

 

CONTROLE

con_codVisita

con_codVisitante

con_flag

 

Assim é bem melhor de programar e entender.... acredite nisso ..

 

espero respostas em quais campos quer amostrar e até mais ...

[Cristian_ 0]

o erro foi falha minha. usa IS NULL e não IS_NULL

 

agora em relação a apagar o registro. para seu banco não ficar inconsistente use contraints(restrições) para apagar registros não permitindo que seja feito caso haja relação com o mesmo em outra tabela. ou apague tudo relacionado.

 

Victor, fiz a alteração e agora ele retorna dados, porém são da tabela controle e não da visitantes. Vou colocar logo abaixo como é o esquema de dados que preciso que retorne.

 

Quanto as constraints obrigado pela dica, não manjo muito de sql mas vou estudar isso pois vai me ajudar.

 

Obrigado pela disposição em ajudar!

[Victor Cometti 8]

quando utiliza v.* especifica todos da tabela VISITANTES.

 

exemplo de contraint

http://forum.imasters.com.br/topic/442618-deletar-dados-de-tres-tabelas-com-o-mesmo-login/page__p__1750794#entry1750794

no manual

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

[Cristian_ 0]

Seu código :

SELECT * FROM visitantes INNER JOIN controle WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

 

 

 

 

Meu Código:

 

SELECT visitantes.codVisitante, visitantes.nomeVisitante, visitantes.rgVisitante, controle.codVisita, controle.codVisitante, controle.flag

FROM visitantes

INNER JOIN controle

on controle.nomeVisitante = visitantes.nomeVisitante

WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Me fale os campos que quer amostrar e observação:

 

Boas práticas de Programação

Faça sua tabela mais prática e fácil de entender: Assim

 

VISITANTES

vis_cod

vis_nome

vis_rg

 

CONTROLE

con_codVisita

con_codVisitante

con_flag

 

Assim é bem melhor de programar e entender.... acredite nisso ..

 

espero respostas em quais campos quer amostrar e até mais ...

 

Olá Wesley!

 

Valeu pela dica, com certeza facilita o trabalho as tabelas no esquema que você mostrou.

 

Agora, sobre o seu código, ele funcionou mas tb traz os dados da tabela CONTROLE.

 

O que gostaria de mostrar seria o campo da tabela VISITANTES:

 

visitantes.nomeVisitante

 

Um exemplo:

 

TABELA VISITANTES

cod nome rg

001 Dada 22.222.222

002 Dede 33.333.333

003 Didi 22.333.222

 

TABELA CONTROLE

cod nome flag data

001 Dede 0 08/11/11

002 Didi 0 09/11/11

003 Dada 0 10/11/11

004 Didi 1 11/11/11

005 Dede 0 11/11/11

006 Dada 1 11/11/11

 

onde:

flag = 1 :: Visitante dentro do prédio, ou seja, não pode aparecer na busca de visitantes justamente porque não pode entrar novamente

flag = 0 :: Deve aparecer na busca de visitantes para que possa ter a entrada liberada (flag passará a ser = 1)

 

Ou seja, neste exemplo ao realizar a busca pelo nome ou RG o retorno deve ser apenas uma linha da tabela VISITANTES:

 

002 Dede 33.333.333

 

Pois os outros dois (Dada e Didi) já estão no prédio (flag=1)

 

O código que passou esta fazendo quase isso, mas esta retornando:

 

001 Dede 0 08/11/11

002 Didi 0 09/11/11

003 Dada 0 10/11/11

005 Dede 0 11/11/11

 

Não sei se fui claro... conseguem me ajudar?

[Motta 645]

Quando se usa AND e OR na mesma consulta é bom usar paranteses.

 

SELECT * 
FROM visitantes INNER JOIN controle 
WHERE ((visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg%) 
AND (controle.flag = 0))

[wesleybob4 0]

Olá Wesley!

 

Valeu pela dica, com certeza facilita o trabalho as tabelas no esquema que você mostrou.

 

Agora, sobre o seu código, ele funcionou mas tb traz os dados da tabela CONTROLE.

 

O que gostaria de mostrar seria o campo da tabela VISITANTES:

 

visitantes.nomeVisitante

 

Um exemplo:

 

TABELA VISITANTES

cod nome rg

001 Dada 22.222.222

002 Dede 33.333.333

003 Didi 22.333.222

 

TABELA CONTROLE

cod nome flag data

001 Dede 0 08/11/11

002 Didi 0 09/11/11

003 Dada 0 10/11/11

004 Didi 1 11/11/11

005 Dede 0 11/11/11

006 Dada 1 11/11/11

 

onde:

flag = 1 :: Visitante dentro do prédio, ou seja, não pode aparecer na busca de visitantes justamente porque não pode entrar novamente

flag = 0 :: Deve aparecer na busca de visitantes para que possa ter a entrada liberada (flag passará a ser = 1)

 

Ou seja, neste exemplo ao realizar a busca pelo nome ou RG o retorno deve ser apenas uma linha da tabela VISITANTES:

 

002 Dede 33.333.333

 

Pois os outros dois (Dada e Didi) já estão no prédio (flag=1)

 

O código que passou esta fazendo quase isso, mas esta retornando:

 

001 Dede 0 08/11/11

002 Didi 0 09/11/11

003 Dada 0 10/11/11

005 Dede 0 11/11/11

 

Não sei se fui claro... conseguem me ajudar?

 

Então ... você quer apenas amostrar os campos de VISITANTES .. ok

 

SELECT visitantes.codVisitante, visitantes.nomeVisitante, visitantes.rgVisitante

FROM visitantes

INNER JOIN controle

on controle.nomeVisitante = visitantes.nomeVisitante

WHERE visitantes.nomeVisitante LIKE %nome% OR visitantes.rgVisitante LIKE %rg% AND controle.flag = 0

 

Com isso só vai mostrar os dados da tabela VISITANTES ..

 

qualquer coisa me add no msn... webicefire@hot......

 

[Cristian_ 0]

Boa noite,

 

Pessoal, fiquei uns dias em outro projeto e agora estou retomando este. Vou testar os códigos e posto o resultado.

 

Valeu

 

-------------

 

Não funcionou ainda :(

 

Me ocorreu o seguinte agora... na tabela de visitantes não tenho a coluna 'flag' (que determina se o visitante esta dentro do prédio ou não) e portanto a busca que realizei utilizando os selects do jeito apresentado acima eles não vi trazer, por exemplo, o nome de um visitante que nunca esteve gravado na tabela controle. Tá certo o raciocínio?

 

Pessoal, alguém me ajuda?

[Mário Monteiro 179]

Voce chegou a mudar a modelagem inicial dos dados que realmente está errada gerando valores duplicados

 

Depois disso é bem simples

[Cristian_ 0]

Sim, ja alterei a modelagem para:

 

Tabela Visitantes

codVisitante

nomeVisitante

rgVisitante

 

Tabela Controle

codVisita

codVisitante

flag

 

Porém ainda não consigo fazer corretamente o filtro da tabela Visitantes utilizando o campo 'flag' da tabela controle.

[Mário Monteiro 179]

teste

 

SELECT * FROM Visitantes INNER JOIN Controle ON Visitantes.codVisitante = Controle.codVisitante WHERE flag = 0

[Cristian_ 0]

Olá Mário,

 

Não deu certo... ele me entrega os codVisitantes apenas da tabela controle. Ex:

 

TABELA VISITANTES

codVisitante nome rg

001 Dada 22.222.222

002 Dede 33.333.333

003 Didi 22.333.222

004 Dudu 99.999.999

 

TABELA CONTROLE

codVisita CodVisitante flag data

001 001 0 08/11/11

002 003 0 09/11/11

003 001 1 10/11/11

004 002 0 11/11/11

005 003 1 11/11/11

 

Realizando a consulta que você (e outros acima) me passaram vou receber como resultado:

 

Dada

Dede

Didi

 

Porém, repare que Dada e Didi aparecem em duas linhas na tabela controle, uma com a flag=0 e outra com a flag=1, e neste caso o que preciso é que eles não retornem no resultado.

 

Além disso repare que na tabela visitantes temos também o Dudu, que por não estar na tabela controle não apareceu nos resultados, e neste caso preciso que ele apareça.

 

Resumindo, preciso trazer todos os nomes da tabela VISITANTES com a condição de que se existirem registros deles na tabela CONTROLE, nenhuma linha deve ter a flag=1.

 

Procurando pela net ja tentei usar subqueries, joins e não consegui realizar a consulta do jeito que preciso.

 

Se alguem matar a charada vai me ajudar muito!!