title: Join 3 Tables with multiple values in other Tables
body: <p>I have 3 tables. need to join them in such a way that I get the ResultTbl</p>
  
  <p>ProductMaster</p>
  
  <pre><code>PID Name   Cost
  1   Car    1000
  2   Bike   500
  </code></pre>
  
  <p>ImageMaster</p>
  
  <pre><code>ImgId PID ImageName
  1      1   car1.png
  2      1   car2.png
  3      1   car3.png
  4      2   Bike1.png
  5      2   Bike2.png
  </code></pre>
  
  <p>BidingMaster</p>
  
  <pre><code>ImgId PID BidCost  userid
  1      1   2000     1
  2      1   2500     2
  3      1   3000     1
  4      2   1200     1
  5      2   900      2
  </code></pre>
  
  <p>UserMaster</p>
  
  <pre><code> UserId Username Usertype
     1      Test 1    1
     2      Test 2    2
  </code></pre>
  
  <p>Usertype</p>
  
  <pre><code>Usertypeid usertypename
  1              admin
  2              regularuser
  </code></pre>
  
  <p>I need to join them in such a way that I get the Max of the cost of the product and 1st row from the Imagemaster.</p>
  
  <p>ResultTbl</p>
  
  <pre><code>PID  Name  Cost  ImgName     username usertype
  1    Car   3000   Car1.png    Test 1    admin
  2    Bike  1200   bike1.png   Test 2    regularuser
  </code></pre>
  
  <p>If someone has not made a bid then the Cost of the product will be displayed but if someone has made the bid then the top bid will be displayed. That again will be joined with 2 more tables to get his username and User type(simple tables).
  I have worked on the SQL code but it keeps missing the result.</p>
  
  <p>This is what I tried..</p>
  
  <pre><code>SELECT a.PID ,a.Name, b.maxAmount, d.Img
  FROM ProductMaster AS a
  LEFT OUTER JOIN (
      SELECT PID, MAX(Amount) AS maxAmount
      FROM BidMaster
      GROUP BY PID
  ) AS b ON a.PID = b.PID
  FULL OUTER JOIN (
      SELECT PID, ImageName AS Img, MAX(ProductId) AS macImg
      FROM ImageMaster
      GROUP BY PID, ImageName
  ) AS d ON a.PID = d.PID
  </code></pre>