Write Query ----

Nona Mohammed 41 Reputation points
2021-09-10T17:18:56.387+00:00

how to write a query to get this result,, that shown in below---131211-55.png

Developer technologies Transact-SQL
SQL Server Other
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 122.5K Reputation points
    2021-09-10T18:11:30.073+00:00

    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
    

  2. EchoLiu-MSFT 14,621 Reputation points
    2021-09-13T02:23:28.413+00:00

    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:
    131393-image.png

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.