Solved

Join with a Top1 one

Posted on 2011-09-02
8
180 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
 

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
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.

 
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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now