MSSQL SELECT-Loop catenate by UNION

I search a solution for the following problem:

There are three tables: [MANDATOR], [PRODUCT] and [CONTRACT]
I want to get all current contracts for every Product for every mandator.
If I make a INNER JOIN over PRODUCT, CONTRACT and MANDATOR, I will
get all contracts and not only the current contract for the relation mandator / product.
I tried to find a solution with ContarctID IN (SELECT TOP 1 ... WHERE ValidFrom <= getdate() ORDER BY ValidFrom DESC) but then I will get only
one contract for on product an not for every mandator / product relation.

So I need something like this:

SELECT * FROM MANDATOR
LOOP
{
     SELECT * FROM PRODUCT p
     INNER JOIN CONTRACT c on c.ProductID = p.ProductID
     AND c.ContractID IN
     (
          SELECT TOP 1 ContractID FROM CONTRACT WHERE ValidFrom <= getdate()
          AND ProductID = p.ProductID ORDER BY ValidFrom DESC
     )
     WHERE MandatorID = [*ID]
     UNION
}
RETURN RESULT

Thanks for help.

Lars
lars_brinkmannAsked:
Who is Participating?
 
aaronakinConnect With a Mentor Commented:
I'd REALLY recommend staying away from using a loop to build a select statement.

Since you are using 2008, you can use the ROW_NUMBER function against the contracts table to return only the current contracts.  Since you didn't provide too many details, I assumed that you are wanting the current contract for each ContractID in the CONTRACT table.  If that's the case, try using the attacthed query.

If this query doesn't work, it's mostly like because I don't know your table structures very well.  It might be beneficial to provide a small sample of data, including columns.
;WITH CTE_CONTRACT AS
(
  SELECT ROW_NUMBER() OVER (PARTITION BY ContractID, ProductID ORDER BY ContractID, ValidFrom DESC, ProductID) AS RowNum
       , *
    FROM CONTRACT
)
SELECT *
  FROM CTE_CONTRACT C
    INNER JOIN PRODUCT P ON C.ProductID = P.ProductID
    INNER JOIN MANDATOR M ON P.ProductID = M.ProductID
  WHERE C.ValidFrom <= GETDATE()
    AND C.RowNum = 1

Open in new window

0
 
tigin44Commented:
if Im not misunderstand you, by using group by you can get the result you want...

SELECT  M.MandatorID, P.ProductID, C.ContractID
FROM MANDATOR M
             LEFT OUTER JOIN PRODUCT P ON M.ProductID = P.ProductID
             LEFT OUTER JOIN  CONTRACT C P.ProductID = C.ProductID
GROUP BY M.MandatorID, P.ProductID, C.ContractID
0
 
lars_brinkmannAuthor Commented:
Thanks for your answer, but is not the solution to my problem.

// different valid from
ContractID  1 | MandatorID 1 | ProductID 1 | ValidFrom 2009-01-01 00:00:00
ContractID  2 | MandatorID 1 | ProductID 1 | ValidFrom 2009-02-01 00:00:00
ContractID  3 | MandatorID 1 | ProductID 1 | ValidFrom 2009-03-01 00:00:00

// different mandator
ContractID  4 | MandatorID 2 | ProductID 1 | ValidFrom 2009-01-01 00:00:00
ContractID  5 | MandatorID 3 | ProductID 1 | ValidFrom 2009-01-01 00:00:00

// different products
ContractID  6 | MandatorID 1 | ProductID 5 | ValidFrom 2009-01-01 00:00:00
ContractID  6 | MandatorID 1 | ProductID 6 | ValidFrom 2009-01-01 00:00:00

I want to have only one current CONTRACT for every MANDATOR-PRODUCT relation.

The Question: give me all contracts for every product for every mandator that is valid now.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
tigin44Commented:
so try this


SELECT  M.MandatorID, P.ProductID, max(C.ContractID)
FROM MANDATOR M
             LEFT OUTER JOIN PRODUCT P ON M.ProductID = P.ProductID
             LEFT OUTER JOIN  CONTRACT C P.ProductID = C.ProductID
GROUP BY M.MandatorID, P.ProductID
WHERE ValidFrom <= getdate()
0
 
lars_brinkmannAuthor Commented:
Sorry but that could not work.
0
 
aaronakinCommented:
How about this?

SELECT *
  FROM CONTRACT C
    INNER JOIN PRODUCT P ON C.ProductID = P.ProductID
    INNER JOIN MANDATOR M ON P.ProductID = M.ProductID
  WHERE C.ValidFrom <= GETDATE()
0
 
lars_brinkmannAuthor Commented:
You will get all Contracts with validFrom <= getdate() and not only the current
 (top 1 ... WHERE validfrom <= getdate() ORDER BY validfrom DESC).
Please see my first comment. Perhaps anybody can give me an answer to my
question: How can I create a loop and concat all SELECTs with UNION

SELECT....
LOOP
{
   SELECT...
   UNION
}




0
 
aaronakinCommented:
What version of SQL Server is this?  2000, 2005, 2008?
0
 
lars_brinkmannAuthor Commented:
2008
0
 
lars_brinkmannAuthor Commented:
Thank you for the solution. Your way is much better than the loop solution I asked for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.