Solved

Join with a Top1 one

Posted on 2011-09-02
8
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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