?
Solved

Join with a Top1 one

Posted on 2011-09-02
8
Medium Priority
?
188 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 2000 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

771 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