SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 trying to join on a top 1 record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evvo1961
Starting Member

4 Posts

Posted - 11/07/2013 :  08:34:27  Show Profile  Reply with Quote
i have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.

however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)

heres my code


SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
	INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
    WHERE [TicketID] = t.TicketID
    ORDER BY DateAssigned ASC) = t.TicketID	
	    
	WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
	INNER JOIN Profiles p ON u.UserID = p.UserID
	INNER JOIN Department d ON d.DepartmentID = p.DepartmentID
	WHERE d.ParentDepartmentID = 3 AND u.Active = 1)
	OR CreatedForUserID NOT IN (SELECT u.UserID FROM Users u
	INNER JOIN Profiles p ON u.UserID = p.UserID
	INNER JOIN Department d ON d.DepartmentID = p.DepartmentID
	WHERE d.ParentDepartmentID = 3 AND u.Active = 1))
    AND (t.DateCreated BETWEEN '05/11/2013' AND '06/11/2013')
	AND RHDUserID IN (SELECT u.UserID FROM Users u
	INNER JOIN Profiles p ON u.UserID = p.UserID
	INNER JOIN Department d ON d.DepartmentID = p.DepartmentID
	WHERE d.ParentDepartmentID = 3 AND u.Active = 1)

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  09:02:47  Show Profile  Reply with Quote

SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
	INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY TicketID ORDER BY DateAssigned) AS Rn,* 
FROM Assignment) a ON a.[TicketID] = t.TicketID
AND a.Rn=1
....


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 11/08/2013 :  01:57:32  Show Profile  Reply with Quote
hi, can you try this?

SELECT 
	t.TicketID, 
	CreatedByUserID, 
	CreatedForUserID, 
	DateCreated, 
	a.DateAssigned 
FROM	Ticket t
	JOIN Assignment a 
	ON a.DateAssigned = (SELECT	MAX(DateAssigned)
				FROM	Assignment
				WHERE	[TicketID] = t.TicketID)

WHERE	EXISTS (SELECT	1
		FROM	Users u
			INNER JOIN Profiles p 
			ON u.UserID = p.UserID
			INNER JOIN Department d 
			ON d.DepartmentID = p.DepartmentID
		WHERE	d.ParentDepartmentID = 3 
			AND u.Active = 1
			AND (CreatedByUserID <> u.UserID OR CreatedForUserID <> u.UserID)
			AND (RHDUserID = u.UserID))
	AND (t.DateCreated BETWEEN '05/11/2013' AND '06/11/2013')

Go to Top of Page

emam razib
Starting Member

Bangladesh
5 Posts

Posted - 11/10/2013 :  21:36:49  Show Profile  Reply with Quote
Hello!
I am also agree with visakh16.
I think it would be..........

unspammed
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
797 Posts

Posted - 11/11/2013 :  14:23:52  Show Profile  Reply with Quote
SELECT ...
FROM Ticket t
CROSS APPLY (SELECT TOP 1 ... FROM Assignment a ORDER BY a.DateAssigned) b

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000