Write Query ----

Question

how to write a query to get this result,, that shown in below---

Answer 1

Try a known method of getting random rows:

select 
    pbt.ProductID, 
    ( select top(1) ProductNum from BcodsTable where ProductID = pbt.ProductID order by newid()) as productNum,
    pt.Name, 
    pt.Describe, 
    pbt.Quantity, 
    pbt.LOWQ
from ProductTable pt
inner join ProdBcodeTable pbt on pbt.ProductID = pt.ID

Answer 2

Hi @Nona Mohammed ,

Please also check:

CREATE TABLE #Bcodstable(ID int,Productid int,ProductNum int)  
INSERT INTO #Bcodstable VALUES(1,12,120),(2,12,122),(3,12,150)  
,(1005,16,250),(1006,16,270),(1007,16,280)  
  
CREATE TABLE #Producttable(ID int,[Name] VARCHAR(25),[Describe] VARCHAR(25))  
INSERT INTO #Producttable VALUES(12,'water','pure'),  
(16,'CocaCola','BC')  
  
CREATE TABLE #Prodbcodetable(Productid int,Quantity int,LOWQ int)  
INSERT INTO #Prodbcodetable VALUES(12,350,50),(16,450,85)  
  
SELECT DISTINCT b.Productid,MIN(b.ProductNum)   
OVER(PARTITION BY b.Productid) ProductNum  
,p.[Name],p.[Describe]  
,pc.Quantity,pc.LOWQ  
FROM #Bcodstable b  
JOIN #Producttable p ON b.Productid=p.ID  
JOIN #Prodbcodetable pc ON b.Productid=pc.Productid  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.