Join 3 Tables with multiple values in other Tables

Question

I have 3 tables. need to join them in such a way that I get the ResultTbl

ProductMaster

PID Name   Cost
1   Car    1000
2   Bike   500

ImageMaster

ImgId PID ImageName
1      1   car1.png
2      1   car2.png
3      1   car3.png
4      2   Bike1.png
5      2   Bike2.png

BidingMaster

ImgId PID BidCost  userid
1      1   2000     1
2      1   2500     2
3      1   3000     1
4      2   1200     1
5      2   900      2

UserMaster

 UserId Username Usertype
   1      Test 1    1
   2      Test 2    2

Usertype

Usertypeid usertypename
1              admin
2              regularuser

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.

ResultTbl

PID  Name  Cost  ImgName     username usertype
1    Car   3000   Car1.png    Test 1    admin
2    Bike  1200   bike1.png   Test 2    regularuser

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.

This is what I tried..

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

You can join 1st and 3rd table and add subquery for 2nd on try this [link](https://stackoverflow.com/a/37963320/10132302) — Dark S, May 08 '20 at 03:29

Dale K · Accepted Answer · 2020-05-08T21:49:13.570

So you can use a sub-query to get the ImageName, and you need a window function to get the relevant BidingMaster row, and then you can join as normal after that.

select P.PID as PID
  , P.[Name] as [Name]
  , coalesce(BM.BidCost, P.Cost) as Cost
  , (select top 1 I.ImageName from dbo.ImageMaster I where I.PID = P.PID order by I.ImgID asc) as ImgName
  , UM.UserName
  , UT.UserTypeName
from dbo.ProductMaster P
left join (
  select PID, ImgID, BidCost, UserId
  from (
    select PID, ImgID, BidCost, UserId
      , row_number() over(partition by PID order by BidCost desc) RowNumber
    from dbo.BidingMaster
  ) BM
  where RowNumber = 1
) BM on BM.PID = P.PID
left join dbo.UserMaster UM on UM.UserId = BM.UserId
left join dbo.UserType UT on UT.UserTypeId = UM.UserType;

@Fhd.ashraf - try that, untested but hopefully you get the idea and can tweak to suit. — Dale K, May 08 '20 at 21:49

score 1 · Answer 2 · edited May 08 '20 at 03:34

1

You can use sub-queries to get max of the biding master table and 1st row from imagemaster before joining

select p.*, a.cost, i.imgname 
from (select pid, max(bidcost) cost from bidingmaster group by pid) a
join (select pid, min(imgid) imgid from imagemaster group by pid) b on b.pid = a.pid
join imagemaster i on i.imgid = b.imgid
join ProductMaster p on p.pid = a.pid

edited May 08 '20 at 03:34

Dale K

25,246
15
42
71

answered May 08 '20 at 03:28

Trang Vu

44
2

score 0 · Answer 3 · answered May 08 '20 at 03:30

You can try with window function row_number().

select 
    PID,
    Name,
    BidCost,
    ImageName
from
(
    select
        bm.PID,
        Name,
        BidCost,
        ImageName,
        row_number() over (partition by bm.PID order by BidCost desc) as rnk
    from BidingMaster bm

    join ImageMaster im
    on bm.PID = im.PID
    anb bm.ImgID = im.ImgId

    join ProductMaster pm
    on bm.PID = pm.PID

) val
where rnk = 1

order by
    PID

Join 3 Tables with multiple values in other Tables

3 Answers3