We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

MSSQL SELECT-Loop catenate by UNION

lars_brinkmann
on
Medium Priority
1,453 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

Commented:
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

Author

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.

Commented:
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()

Author

Commented:
Sorry but that could not work.
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()

Author

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
}




What version of SQL Server is this?  2000, 2005, 2008?

Author

Commented:
2008
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you for the solution. Your way is much better than the loop solution I asked for.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.