join e subquery

Por exemplo num consultório que tenha um médico responsável à cada venda realizada..

Poderiam haver no mínimo 3 tabelas (coloquei só as info relevantes): medico (id (pk), nome) remedio (idremedio (pk), nome) medico_venda (idvenda (pk), remedio(pk e fk), medico(fk), quant_venda)

A tabela medico_venda vai ter um id para cada venda e trará como chave estrangeira o remédio que está sendo vendido e o médico responsável. No caso da compra de mais de um remédio, o idvenda vai se repetir em mais de uma linha . Então é necessário aplicar uma chave primária composta que será o id da venda e o id do remédio, pois o remédio não se repetirá em mais de uma linha.

Para por exemplo ver todas as vendas de todos os médicos, os remédios e as quantidades vendidas é só fazer uma query como essa:

SELECT medico.nome, remedio.nome, venda.quantidade from medico 
join venda on (medico.idmedico = venda.medico)
join remedio on (remedio.idremedio = venda.remedio);

Se quiser ser mais específico quanto algum médico em questão pode adicionar um where no final. Por ex:

SELECT medico.nome, remedio.nome, venda.quantidade from medico 
join venda on (medico.idmedico = venda.medico) 
join remedio on (remedio.idremedio = venda.remedio) 
where medico.nome = "luiz";

Existem muitas possibilidades para buscar informação, tentei ser o mais breve possível, espero ter ajudado em algo!

Olá cadujuca!

Seguindo de acordo como o professor mostra no vídeo, você poderia fazer uma query, usando apenas o INNER JOIN e o GROUP BY, assim você vai conseguir fazer o join entre as duas tabelas, e depois agrupar as informações para que por fim você consiga fazer a função SUM (que é uma função de agregação, por isso você é obrigado a utilizar o GROUP BY), porém não vejo a necessidade de uma subquery nesse consulta, você pode usar o comando assim:

SELECT medicos.id, medicos.crm, medicos.medico, 
SUM(receitas.quantidade) AS QUANTIDADE_VENDIDA_POR_MEDICO,
SUM(receitas.valor_total) AS VALOR_TOTAL_VENDIDO_POR_MEDICO
FROM medicos
INNER JOIN receitas
ON medicos.id = receitas.medico_id
GROUP BY medicos.id;

Com essa consulta você vai conseguir ver o id do médico (da tabela médicos), o CRM do médico (da tabela médicos), o nome do do médico (também da tabela médicos), e a QUANTIDADE_VENDIDA_POR_MEDICO bem como o VALOR_TOTAL_VENDIDO_POR_MEDICO, porque a função SUM soma todos os valores dos campos quantidade (da tabela de receitas) e também todos os campos valor_total (da tabela de receitas).

Espero ter ajudado!