Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL SELECT-Loop catenate by UNION

Posted on 2009-02-13
10
Medium Priority
?
1,378 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
0
Comment
Question by:lars_brinkmann
  • 5
  • 3
  • 2
10 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 23634420
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
 

Author Comment

by:lars_brinkmann
ID: 23634619
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
 
LVL 26

Expert Comment

by:tigin44
ID: 23634701
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:lars_brinkmann
ID: 23635169
Sorry but that could not work.
0
 
LVL 11

Expert Comment

by:aaronakin
ID: 23636975
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
 

Author Comment

by:lars_brinkmann
ID: 23645601
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
 
LVL 11

Expert Comment

by:aaronakin
ID: 23649688
What version of SQL Server is this?  2000, 2005, 2008?
0
 

Author Comment

by:lars_brinkmann
ID: 23650059
2008
0
 
LVL 11

Accepted Solution

by:
aaronakin earned 2000 total points
ID: 23650517
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
 

Author Closing Comment

by:lars_brinkmann
ID: 31546658
Thank you for the solution. Your way is much better than the loop solution I asked for.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question