Solved

Join with a Top1 one

Posted on 2011-09-02
8
185 Views
Last Modified: 2012-05-12
I am a pretty complex view that I am working with and now I need to add an additional join to the view.

I basically have a table with projects, but now I want to add another table that should only have one record per project, but there could be more.  The records in the new table will tell me if a calculation should be made or not.

here is what I am trying to do, but it isnt working

            LEFT JOIN (SELECT TOP 1 Projectnumber AS projectnumber, status, hid FROM dbo.Absences WHERE TYPE = 232 )
            AS a ON projectnumber=prproject.projectnr


           
0
Comment
Question by:red_75116
  • 3
  • 3
  • 2
8 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 36476083
That query will only return 1 row, regardless of the amount of existing ProjectNumbers.
What error are you getting?

If there's no error and you want all the ProjectNumbers, you could try:

LEFT JOIN (SELECT Projectnumber AS projectnumber, status, hid FROM dbo.Absences WHERE TYPE = 232 GROUP BY Projectnumber, status, hid)
            AS a ON projectnumber=prproject.projectnr

This will get you every different ProjectNumber, status and hid. Will this work for you?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 36476103
Sorry, the subquery should be

SELECT Projectnumber  FROM dbo.Absences WHERE TYPE = 232 GROUP BY Projectnumber


To get every different projectNumber in the table.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36476680
USE a windowing function ... Row_number() Over  and partition by the project number...

now specify the set of COLUMNS TO DETERMINE LATEST which can be used to order the rows
so that you get the information for the project you desire  by selecting the relative row_number 1 for
each set of project data.....
LEFT outer JOIN (SELECT Projectnumber AS projectnumber, status, hid
                    FROM (select a.*
                                ,row_number() OVER (PARTITION BY PROJECTNUMBER
                                    ORDER BY COLUMNS TO DETERMINE LATEST) AS RN
                            from dbo.Absences as a
                           WHERE TYPE = 232 ) AS X
                                  WHERE RN=1) AS A 
        ON a.projectnumber=prproject.projectnr

Open in new window

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:red_75116
ID: 36477973
LIONKING:

The issue is there are 10,000 project records, but only 1600 have been "Sold".  Therefore there should be 1600 records with a type =232 in the other table, but it is possible someone could create more than one of those records for a project.

So I want a query that will show all 10,000 records and only one of the records if there are more than one for that project.  If there happend to be two records in the other table for Project 100 then I only want one of them, not both cause I dont want two results lines

Project# | Descr| Status| OtherTableID | Other Table Values
100      | projectA| Sold   |  2001   |  X
100      | projectA| Sold   |  2002   |  X  (DONT WANT THIS LINE IF IT EXISTS)
101     | projectB  | Lost  | NULL   | NULL
110     | projectX| Sold   |  2500   |  X
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 36477981
Grouping the way I did in the second post would give you the results you need, and display only one project number when the type is 232. If you need another condition about the status you can do something like this as well:

LEFT JOIN (SELECT DISTINCT Projectnumber FROM dbo.Absences WHERE TYPE = 232 AND status='Sold')
            AS a ON a.projectnumber=prproject.projectnr
0
 

Author Comment

by:red_75116
ID: 36478044
The sold status is on the project record.  I am now trying to validate the results to see if it is what I need.

Thanks!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36478075
select ....
  from (
select p.*,a.*
     ,row_number() over (partition by p.projectnr order by othertableid) as rn
  from project as p
  left outer join absences as a
   on p.projectnr=a.projectnumber
  where p.status='sold'
) as x
where rn=1
order by projectnr
0
 

Author Closing Comment

by:red_75116
ID: 36478809
I tried all the suggestions and this was the only one that worked.  The others looked right, but producted more than the number of jobs sold.  I dont understand this logic, but it appears to be working.

Thanks to all
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

820 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