CODEWITHSUNDEEP
mysqlsqlselectjoininner-join
PeakGen
PeakGen

Reputation: 23035

How can I refer to a field being referred by the SELECT query?

I have 2 tables, Ongoing_Portfolio and Ongoing_Fees. Ongoing fees contains a foreign key for Ongoing-Portfolio. Below are the tables

enter image description here enter image description here

Please have a look at the below code

SELECT Ongoing_Portfolio.*, 
Ongoing_Fees.Ongoing_Net_Income FROM Ongoing_Portfolio 
INNER JOIN Ongoing_Fees ON Ongoing_Fees.idPortfolio = Ongoing_Portfolio.idPortfolio 
WHERE Ongoing_Portfolio.idPortfolio = 1 
AND 
Ongoing_Portfolio.idOngoing_Portfolio = ?

Can you see the ? mark there in the last row? What I wanted to do there is to refer the idOngoing_Portfolio field of the Ongoing_Fees which is being refereed by the query at the moment. In more detail, I need something like below.

 AND 
    Ongoing_Portfolio.idOngoing_Portfolio = idOngoing_Fees of the Ongoing_Fees table where the query is currently accessing

How can I do this in mysql?

Upvotes: 1

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

The direct answer to your question is to put the column in as you described it:

SELECT op.*, 
Ongoing_Fees.Ongoing_Net_Income
FROM Ongoing_Portfolio op INNER JOIN
     Ongoing_Fees onf
     ON onf.idPortfolio = op.idPortfolio 
WHERE op.idPortfolio = 1 AND 
      op.idOngoing_Portfolio = onf.idOngoing_Fees;

Does this do what you want? If not, you might consider asking another question. Putting the actual create table statements is more useful than a picture of two tables. Sample data and desired results are a big help both for you to understand what you want to do and to convey this information to others.

Upvotes: 2

Related Questions