Select to bring specific id record

Question

I wonder how do I bring a specific record of a fk, being that I have the table lodging and the table consumption (with fk codHospedagem). When I select some hosting, I click on the consumption button of my consumption screen.

I need to bring in the consumption records of that hosting that I select, because my select is not working, only brings the empty line. Below the code:

SELECT c.codConsumo as Consumo,c.codHospedagem as Hospedagem , 
p.nomeProduto AS Produto, 
c.quantidade, c.valorConsumo, c.status 
FROM consumo c   
INNER JOIN  produto AS p ON p.codProduto = c.codProduto                   
INNER JOIN hospedagem AS H ON H.codHospedagem = C.codHospedagem                 
WHERE H.codHospedagem = c.codConsumo
ORDER BY c.codConsumo ; 

Answer 1

Check the syntax of where because you are filtering the hosting by the consumption code, and not the consumption related hosting.

Exemplifying:

In your code, H.codHospedagem relates to C.CodHospedagem, then on his where shall contain a reference to lodging - and not to consumption.

SELECT c.codConsumo as Consumo,c.codHospedagem as Hospedagem , 
p.nomeProduto AS Produto, 
c.quantidade, c.valorConsumo, c.status 
FROM consumo c   
INNER JOIN  produto AS p ON p.codProduto = c.codProduto                   
INNER JOIN hospedagem AS H ON H.codHospedagem = C.codHospedagem                 
WHERE H.codHospedagem = /*c.codConsumo*/
ORDER BY c.codConsumo ;