LEFT JOIN top 1

Quite an easy one i'm sure but can't get my head around it..

I have a jobs table and an items table (one to many)

I want to select all records from jobs and only record from items (the min date)

Jobs
-----
JobNo
other data..

Items
ItemID
JobNo
myDate

My results should only return one row from items based on the MIN(mydate).

The query should still return records from jobs if  1) No rows matched in items and 2) null in mydate column

thanks guys
LVL 9
Type25Asked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
tis shud do it
select job.*, item.*
from job
left join
 (select x.job_no, minmydate, min(itemid) as minitemid
  from
   (select job_no, min(isnull(mydate, '9900-01-01')) as minmydate from item group by job_no) x
  inner join item on x.job_no = item.job_no and x.minmydate = isnull(item.mydate, '9900-01-01')
  group by x.job_no, minmydate) y on job.job_no = y.job_no
left join item on item.itemid = y.minitemid

Open in new window

0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
select j.*,i.itemid,i.myDate
from jobs j
join
(select itemid,jobno,min(myDate)
from items
group by itemid,jobno) i
ON i.jobno = j.jobno
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
yep - and that should be a LEFT join (as you specified...)


select j.*,i.itemid,i.myDate
from jobs j
left join
(select itemid,jobno,min(myDate)
from items
group by itemid,jobno) i
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Sham HaqueSenior SAP CRM ConsultantCommented:
(damn this too-quick posting lark):
on point 2, about NULLs in mydate:


or some such early date... and of course give it a column alias
select j.*,i.itemid,i.myDate
from jobs j
left join 
(select itemid,jobno,min(ISNULL(myDate,'01/01/1950')) myDate
from items
group by itemid,jobno) i

Open in new window

0
 
imitchieCommented:
inner query x takes care of null mydates, but gives it a date such that other records with valid mydate is preferred
inner query y takes care of filtering for exactly one item if more than one matches min mydate
final query selects all job and matches up with available item data
0
 
Type25Author Commented:
This is what i ended up with, pretty sure that's okay?


SELECT     dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName, 
                      dbo.LicencesHeld.LicUserName, i.RepDate
FROM         dbo.Jobs INNER JOIN
                      dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
			(select top 1 itemid,jobno,min(ISNULL(ReplacementLetterSent,'01 Jan 1950')) RepDate from items y group by itemid,y.jobno) i
			ON i.jobno = Jobs.jobno
 
WHERE     (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND 
                      dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
 
ORDER BY Jobs.JobNo

Open in new window

0
 
Marcel HopmanCommented:

SELECT		J.*
,		ISNULL(I.ItemID, '<< No Item >>'
FROM		Jobs J
LEFT JOIN	(
		SELECT 	ItemID
		,      	JobNo
		FROM	Items
		JOIN	(
			SELECT		ItemID
			,		JobNo
			,		MIN(MyDate) AS MinDate
			FROM		Items
			GROUP BY	ItemID
			) M
		ON	M.ItemID = I.ItemID
		AND	M.JobNo = I.JobNo
		AND	M.MinDate = I.MyDate
		) I
ON		I.JobNo = J.JobNo

Open in new window

0
 
Type25Author Commented:
ok this doesn't work:

SELECT     dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
                      dbo.LicencesHeld.LicUserName, i.itemid
FROM         dbo.Jobs INNER JOIN
                      dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
                  (select itemid,jobno,min(ISNULL(ReplacementLetterSent,'01 Jan 1950')) RepDate from items y group by itemid,y.jobno) i
                  ON i.jobno = Jobs.jobno

WHERE     (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
                      dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))

ORDER BY Jobs.JobNo

It's selecting more than one record per job, which looking at it you can see.
If i add in TOP 1 into the select join query then it doesn't return anything..

any ideas?
0
 
Rajesh_mjCommented:
Hi,

just try:

select j.*,itm.itemid,itm.myDate
from jobs j left
join
(select Min(itemid) as itemid,jobno,min(myDate) as mydate
from items
group by jobno)as itm
ON j.jobno = itm.jobno
0
 
JR2003Commented:
SELECT J.*
  FROM jobs J
 LEFT JOIN (SELECT JobNo, Min(mydate) myDate
              FROM items
             GROUP BY JobNo) AS I
        ON I.JobNo = J.JobNo
0
 
Rajesh_mjCommented:

Just try this(my first script was not correct):

select j.jobno,Jobs.ContactsSent,itm.myDate,min(item.itemid)
from jobs j left
join
(select jobno,min(myDate) as mydate
from items
group by jobno)as itm
ON j.jobno = itm.jobno
left join items On itm.jobno = items.jobno
and itm.mydate = items.mydate
group by j.jobno,Jobs.ContactsSent,itm.myDate
0
 
imitchieCommented:
i merged your table/column names with my first proposal
declare @FromDate datetime
declare @toDate datetime
 
SELECT     dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
                      dbo.LicencesHeld.LicUserName, i.itemid
FROM         dbo.Jobs INNER JOIN
                      dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
 
 (select x.jobno, minReplacementLetterSent, min(itemid) as itemid
  from
   (select jobno, min(isnull(ReplacementLetterSent, '9900-01-01')) as minReplacementLetterSent from item group by jobno) x
  inner join item on x.jobno = item.jobno and x.minReplacementLetterSent = isnull(item.ReplacementLetterSent, '9900-01-01')
  group by x.jobno, minReplacementLetterSent) y on y.jobno = Jobs.jobno
 
WHERE     (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
                      dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
 
ORDER BY Jobs.JobNo

Open in new window

0
 
imitchieCommented:
code testing visible. please drop the two declare @.. datetimes from the top
0
 
imitchieCommented:
well ignore it completely. just use the below
SELECT     dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
                      dbo.LicencesHeld.LicUserName, i.itemid
FROM         dbo.Jobs INNER JOIN
                      dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
 
 (select x.jobno, minReplacementLetterSent, min(itemid) as itemid
  from
   (select jobno, min(isnull(ReplacementLetterSent, '9900-01-01')) as minReplacementLetterSent from item group by jobno) x
  inner join item on x.jobno = item.jobno and x.minReplacementLetterSent = isnull(item.ReplacementLetterSent, '9900-01-01')
  group by x.jobno, minReplacementLetterSent) i on i.jobno = Jobs.jobno
 
WHERE     (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
                      dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
 
ORDER BY Jobs.JobNo

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.