SQL mais uma ajuda por favor

Bro840

Power Member
Para ser mais claro de se perceber a BD trata dados relativos a futebol.

Ora vamos então à minha dúvida.

Tenho duas tabelas:

ENCONTROS
ID
ID_COMPETICAO
JORNADA
VISITADO
VISITANTE
DATA

GOLOS
ID
ID_ENCONTRO
EQUIPA
FORMA
MINUTO


Quero fazer uma query que me devolva a tabela completa ENCONTROS já com os devidos joins para os campos VISITADO e VISITANTE, o que consigo fazer desta forma:

SELECT [E].ID, [E].ID_COMPETICAO, [E].JORNADA, EQUIPA1.EQUIPA AS VISITADO, EQUIPA2.EQUIPA AS VISITANTE, [E].DATA_HORA
FROM (ENCONTROS AS E
LEFT JOIN EQUIPAS AS EQUIPA1
ON [E].VISITADO=EQUIPA1.ID)
LEFT JOIN EQUIPAS AS EQUIPA2 ON [E].VISITANTE=EQUIPA2.ID
WHERE [E].ID_COMPETICAO= **VARIAVEL**
ORDER BY [E].ID

E junte mais duas colunas, que seriam respectivamente o total de golos da equipa VISITADO e o total de golos da equipa VISITANTE. Seria uma espécie de duplo union acho eu...
 
Código:
[COLOR=#ededed]SELECT [/COLOR][COLOR=#EDEDED]*etc..*[/COLOR][COLOR=#ededed],GOLOS_EQUIPA1.num_golos as GOLOS_VISITADO, GOLOS_EQUIPA2.num_golos [/COLOR][COLOR=#EDEDED]as GOLOS_VISITANTES[/COLOR][COLOR=#ededed]
[/COLOR][INDENT][COLOR=#ededed]FROM [/COLOR][COLOR=#EDEDED]*etc..*[/COLOR][COLOR=#ededed],
[/COLOR][/INDENT]
[INDENT=2][COLOR=#ededed](SELECT COUNT(*) as num_golos FROM GOLOS WHERE [/COLOR][COLOR=#EDEDED]GOLOS.ID_ENCONTRO = [E].ID AND GOLOS.[/COLOR][COLOR=#EDEDED]EQUIPA=EQUIPA1.EQUIPA[/COLOR][COLOR=#ededed]) as GOLOS_EQUIPA1, [/COLOR][COLOR=#EDEDED](SELECT [/COLOR][COLOR=#EDEDED]COUNT[/COLOR][COLOR=#EDEDED](*) as num_golos FROM GOLOS [/COLOR][COLOR=#ededed]WHERE [/COLOR][COLOR=#EDEDED]GOLOS.ID_ENCONTRO = [E].ID AND GOLOS.[/COLOR][COLOR=#EDEDED]EQUIPA=EQUIPA2.EQUIPA[/COLOR][COLOR=#EDEDED]) as GOLOS_EQUIPA2
[/COLOR][/INDENT]
[INDENT][COLOR=#EDEDED]WHERE *etc..*
[/COLOR][/INDENT]



Vou ser honesto, não testei, mas a lógica está aqui. Eu teria feito o resultado em duas linhas, onde cada linha correspondia a uma equipa e tinha os totais de golos com o groupby "equipa_id".

Edit: Lembrei-me que provavelmente é preciso meteres as condições dentro dos SELECTS do FROM porque devolvem só o count, ou seja, não é possível depois fazer referência aos campos id_encontro/equipa no WHERE visto que não existem fora desses SELECTS(com alias GOLOS_EQUIPAX).
 
Última edição:
Ryu antes de mais obrigado pelo esforço.

Depois de acabar de escrever este post vou testar o teu script. Consigo perceber mais ou menos a lógica do teu script pelo que penso que vai ser uma questão de tentativa erro até chegar lá.

A tua ideia, se bem percebi era juntar à tabela encontros mais dois campos, sendo eles o resultado do visitado e o resultado do visitante, ficando os detalhes dos mesmos para a minha outra tabela, a tabela golos onde pelo ID_encontro poderia sacar mais informações detalhadas?

Se for essa a ideia, era a solução que eu próprio tinha imaginado para partir este galho, mas vou deixar essa possibilidade em aberto mas testar o teu script, nem que seja pela aprendizagem!


Muito obrigado
 
Acho que não fui a tempo de editar antes de experimentares a minha solução inicial...(Editei o código para reflectir a ideia do "edit:")

Caso não dê ainda há a possibilidade de simplesmente fazer as tabelas GOLOS_EQUIPAX devolver * e depois fazer o COUNT no SELECT "principal", podendo limitar no WHERE "principal" os valores do id_encontro/equipa de ambos os SELECTS:

SELECT *etc*, COUNT(GOLOS_EQUIPA1.ID) as GOLOS_VISITADO, COUNT(GOLOS_EQUIPA2.ID) as GOLOS_VISITADO
FROM *etc*, (SELECT * FROM GOLOS) as GOLOS_EQUIPA1,(SELECT * FROM GOLOS) as GOLOS_EQUIPA2
WHERE GOLOS_EQUIPA1.EQUIPA = EQUIPA1.ID AND
GOLOS_EQUIPA2.EQUIPA = EQUIPA2.ID
GOLOS_EQUIPA1.ID_ENCONTRO = [E].ID
GOLOS_EQUIPA2.ID_ENCONTRO = [E].ID

Peço desculpa não estar a acertar, mas esqueço-me de certos pormenores, para além de estar enferrujado.
Mas desta última solução não consigo ver falha imediata, embora na minha primeira resposta (com edit) não tenha a certeza se dentro dos SELECTS consigo fazer a filtragem ou não do WHERE. É provável que ambas funcionem embora aposte mais nesta.
 
Última edição:
Simplesmente é só substituires no meu codigo nos SELECT, FROM e WHERE os *etc* pelo o que tu tens nos teus SELECT,FROM e WHERE. Em princípio deve dar visto que é independente, ou melhor, o meu código é que depende do teu.

E caso não te tenhas apercebido eis uma pequena explicação do que foi feito: no FROM tu especificas tabelas, ou onde ir buscar os dados. Uma tabela não é nada mais nada menos que colunas e linhas, ou seja, o resultado de um SELECT é considerado uma tabela. Daí ser possível utilizar os dados de um SELECT num FROM.
 
Última edição:
SELECT [E].ID, [E].ID_COMPETICAO, [E].JORNADA, EQUIPA1.EQUIPA AS VISITADO, EQUIPA2.EQUIPA AS VISITANTE, [E].DATA_HORA, COUNT(GOLOS_EQUIPA1.ID) as GOLOS_VISITADO, COUNT(GOLOS_EQUIPA2.ID) as GOLOS_VISITANTE


FROM (ENCONTROS AS E LEFT JOIN EQUIPAS AS EQUIPA1 ON [E].VISITADO=EQUIPA1.ID) LEFT JOIN EQUIPAS AS EQUIPA2 ON [E].VISITANTE=EQUIPA2.ID, (SELECT * FROM GOLOS) as GOLOS_EQUIPA1,(SELECT * FROM GOLOS) as GOLOS_EQUIPA2


WHERE [E].ID_COMPETICAO=13


AND GOLOS_EQUIPA1.EQUIPA = EQUIPA1.ID
AND GOLOS_EQUIPA2.EQUIPA = EQUIPA2.ID
AND GOLOS_EQUIPA1.ID_ENCONTRO = [E].ID
AND GOLOS_EQUIPA2.ID_ENCONTRO = [E].ID


ORDER BY [E].ID;


O raciocínio e a lógica tenho de concordar estão bem.

Se fazer os Lefts Join ontem foi difícil isto hoje está a elevar o nível... Sou programador domestico aprendi tudo sozinho e até agora nunca tinha precisado de conhecimentos deste calibre em SQL, btw ;) portanto só para explicar que com querys deste nível fico meio as aranhas apesar de perceber o pensamento.

Quando corro a query do inicio deste post diz "operação join não suportada"....
 
Left join -> Inclui todos os registos da tabela da esquerda, se calhar está a dar erro por causa disso.

Em vez de fazeres joins podes simplesmente meter na clausula WHERE as condições que fazem o join:

FROM ENCONTROS AS E, EQUIPAS AS EQUIPA1, EQUIPAS AS EQUIPA2, (SELECT * FROM GOLOS) as GOLOS_EQUIPA1,(SELECT * FROM GOLOS) as GOLOS_EQUIPA2

WHERE [E].ID_COMPETICAO=13
AND [E].VISITADO=EQUIPA1.ID
AND [E].VISITANTE=EQUIPA2.ID
AND GOLOS_EQUIPA1.EQUIPA = EQUIPA1.ID
AND GOLOS_EQUIPA2.EQUIPA = EQUIPA2.ID
AND GOLOS_EQUIPA1.ID_ENCONTRO = [E].ID
AND GOLOS_EQUIPA2.ID_ENCONTRO = [E].ID

Testa desta maneira. Também fica ligeiramente mais fácil de ler acho. Desta maneira ou faz INNER JOIN em vez de LEFT JOIN. Inner join faz com que o resultado do JOIN sejam registos com ligações, ou seja, que estejam referenciados.
 
Não sei se isto corresponde ao que necessitas, mas aqui fica:

select e.id, e.id_competicao, e.jornada, e.visitado, tvisitado.total, e.visitante, tvisitante.total, e.data
from encontros e
left join (select equipa, count(*) as total from golos group by equipa) as tVisitado on tvisitado.equipa = e.visitado
left join (select equipa, count(*) as total from golos group by equipa) as tVisitante on tVisitante.equipa = e.visitante
where
e.id_competicao = _whatever_
order by _whatever_

A ideia é contar o total de golos das 2 equipas utilizando para isso o group by, nas sub-queries dos left joins, para que depois possas utilizar os totais na query exterior.
Não sei se funciona correctamente, porque não tenho aqui nada à mão para testar, mas aqui fica a tentativa :P

... desculpa ... descarta esta solução. Estava a ler na diagonal mas isto não é o que procuras.
 
Última edição:
Não sei se isto corresponde ao que necessitas, mas aqui fica:

select e.id, e.id_competicao, e.jornada, e.visitado, tvisitado.total, e.visitante, tvisitante.total, e.data
from encontros e
left join (select equipa, count(*) as total from golos group by equipa) as tVisitado on tvisitado.equipa = e.visitado
left join (select equipa, count(*) as total from golos group by equipa) as tVisitante on tVisitante.equipa = e.visitante
where
e.id_competicao = _whatever_
order by _whatever_

A ideia é contar o total de golos das 2 equipas utilizando para isso o group by, nas sub-queries dos left joins, para que depois possas utilizar os totais na query exterior.
Não sei se funciona correctamente, porque não tenho aqui nada à mão para testar, mas aqui fica a tentativa :P

Só um pormenor... Isto fica os totais por competição e não por encontro visto que não se está a limitar o SELECT da tabela GOLOS
 
Não. Tal como o Ryu disse eu me apercebi depois de por ali a resposta, o que eu te disse contabiliza os golos totais das equipas e não os golos das equipas por encontro.
Agora não tenho tempo, mas amanha de manhã se ninguem te tiver respondido com a solução eu tento dar uma maozinha ;)
 
Sardaukar é isto mesmo mas agora voltei a ficar com os campos Visitado e Visitante com números lol

Isso é porque estás a ir buscar o ID e não o nome. Tens que fazer inner join com as equipas de modo a ir buscar o campo EQUIPA que deve corresponder à string.

E honestamente não consigo perceber porque dá o erro no código que sugeri. Podes dizer a linha exacta?
 
Não. Tal como o Ryu disse eu me apercebi depois de por ali a resposta, o que eu te disse contabiliza os golos totais das equipas e não os golos das equipas por encontro.
Agora não tenho tempo, mas amanha de manhã se ninguem te tiver respondido com a solução eu tento dar uma maozinha ;)

Agradecia
 
Já tentaste fazer inner joins como sugeri? É que left joins faz com que todos os registos das tabela à esquerda permaneçam. O comportamento que deves querer é de ligação única. Só mostra os registos que têm as ligações especificadas e mais nenhum.

EX:

Table1:
id:nome:
1:xpto
2:abc
3:cba

Table2:
id:id_table1:nome
1:1:nome1
2:1:nome2
3:2:nome3

select table1.* from table1 LEFT JOIN table2 ON table1.id = table2.id_table1
resultado:
1:xpto
2:abc
3:cba

Inner join
resultado:
1:xpto
2:abc
 
Última edição:
Isso é porque estás a ir buscar o ID e não o nome. Tens que fazer inner join com as equipas de modo a ir buscar o campo EQUIPA que deve corresponder à string.

E honestamente não consigo perceber porque dá o erro no código que sugeri. Podes dizer a linha exacta?

Eu sei que tenho de fazer join mas o problema é que quando faço mais de dois join diz: Operação join não suportada. Estou a trabalhar em access...

Ryu não diz a linha diz apensa o erro
 
Ryu nós ia-mos aqui:

SELECT [E].ID, [E].ID_COMPETICAO, [E].JORNADA, EQUIPA1.EQUIPA AS VISITADO, EQUIPA2.EQUIPA AS VISITANTE, [E].DATA_HORA, COUNT(GOLOS_EQUIPA1.ID) AS GOLOS_VISITADO, COUNT(GOLOS_EQUIPA2.ID) AS GOLOS_VISITANTE


FROM ENCONTROS AS E, EQUIPAS AS EQUIPA1, EQUIPAS AS EQUIPA2, (SELECT * FROM GOLOS) AS GOLOS_EQUIPA1, (SELECT * FROM GOLOS) AS GOLOS_EQUIPA2


WHERE [E].ID_COMPETICAO=13
And [E].VISITADO=EQUIPA1.ID
And [E].VISITANTE=EQUIPA2.ID
And GOLOS_EQUIPA1.EQUIPA=EQUIPA1.ID
And GOLOS_EQUIPA2.EQUIPA=EQUIPA2.ID
And GOLOS_EQUIPA1.ID_ENCONTRO=[E].ID
And GOLOS_EQUIPA2.ID_ENCONTRO=[E].ID


ORDER BY [E].ID;
 
Ryu nós ia-mos aqui:

SELECT [E].ID, [E].ID_COMPETICAO, [E].JORNADA, EQUIPA1.EQUIPA AS VISITADO, EQUIPA2.EQUIPA AS VISITANTE, [E].DATA_HORA, COUNT(GOLOS_EQUIPA1.ID) AS GOLOS_VISITADO, COUNT(GOLOS_EQUIPA2.ID) AS GOLOS_VISITANTE


FROM ENCONTROS AS E, EQUIPAS AS EQUIPA1, EQUIPAS AS EQUIPA2, (SELECT * FROM GOLOS) AS GOLOS_EQUIPA1, (SELECT * FROM GOLOS) AS GOLOS_EQUIPA2


WHERE [E].ID_COMPETICAO=13
And [E].VISITADO=EQUIPA1.ID
And [E].VISITANTE=EQUIPA2.ID
And GOLOS_EQUIPA1.EQUIPA=EQUIPA1.ID
And GOLOS_EQUIPA2.EQUIPA=EQUIPA2.ID
And GOLOS_EQUIPA1.ID_ENCONTRO=[E].ID
And GOLOS_EQUIPA2.ID_ENCONTRO=[E].ID


ORDER BY [E].ID;

Já sei porquê... O encontro não é único. Podem haver vários encontros numa competição certo? Logo é preciso especificar o encontro. (adicionar AND [E].ID = X depois da competição)

Neste caso nem precisas do ID da competição, só do encontro.
 
Back
Topo