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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,114 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 114K 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,576 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.