Solved

Join with a Top1 one

Posted on 2011-09-02
8
183 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

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.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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