Solved

LEFT JOIN top 1

Posted on 2007-11-15
14
1,858 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:Type25
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20288466
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
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20288468
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
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20288481
(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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20288484
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20288503
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
 
LVL 9

Author Comment

by:Type25
ID: 20288526
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
 
LVL 6

Expert Comment

by:Marcel Hopman
ID: 20288554

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 9

Author Comment

by:Type25
ID: 20288630
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20288698
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
 
LVL 18

Expert Comment

by:JR2003
ID: 20288714
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20288925

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
 
LVL 25

Expert Comment

by:imitchie
ID: 20291867
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20291870
code testing visible. please drop the two declare @.. datetimes from the top
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20291875
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

14 Experts available now in Live!

Get 1:1 Help Now